ODBC connection

Hi,
I would like to have a ODBC connection to a MSDE 2000 database on a Win 2003
server from a client PC. Can this be done?
On the client I can browse to the the SQL server, but I cannot connect to
it. Is there a way to connect and retreive the data ?
Arnold
What does it mean "cannot connect to it"? How do you do it, any error
message? Yes, there is way to connect to it, as long as you do it right.
The simplest way would be doing it from Control Panel->Administrative
Tools->ODBC Data Source and follows the wizard and make correct selection.
"Arnold" <Arnolddegreef@.quicknet.punt.nl> wrote in message
news:uFNlJ9vwHHA.3364@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I would like to have a ODBC connection to a MSDE 2000 database on a Win
> 2003 server from a client PC. Can this be done?
> On the client I can browse to the the SQL server, but I cannot connect to
> it. Is there a way to connect and retreive the data ?
> Arnold
>
|||Hi Norman,
I can do what you suggest on the server, but I cannot do it on a PC
connected to the same server.
If I connect from a PC I get a message that either the SQL database does not
exist, or the username and password is not OK.
When I type in exactly the same settings on the server it all works OK. (I
have not got the exact message for you since it is in Dutch)
So it seems that I am not allowed to connect to the database via ODBC on a
PC. Is that correct, or is there another way to do this ?
Arnold
"Norman Yuan" <NotReal@.NotReal.not> schreef in bericht
news:e%23zxmswwHHA.4736@.TK2MSFTNGP04.phx.gbl...
> What does it mean "cannot connect to it"? How do you do it, any error
> message? Yes, there is way to connect to it, as long as you do it right.
> The simplest way would be doing it from Control Panel->Administrative
> Tools->ODBC Data Source and follows the wizard and make correct selection.
> "Arnold" <Arnolddegreef@.quicknet.punt.nl> wrote in message
> news:uFNlJ9vwHHA.3364@.TK2MSFTNGP02.phx.gbl...
>
|||After Windows 2000 SP2, MSDE was blinded and its ports were shut off. This
prevents it from being attacked by the slammer virus? worm...-whatever. These
ports and protocols must be reenabled for the service to be visible on the
network. Yes, you can connect via ODBC but there are a lot of other
alternatives that are more efficient. Are you building a .NET application,
VB6 or what?
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Arnold" <Arnolddegreef@.quicknet.punt.nl> wrote in message
news:eZYofzwwHHA.2040@.TK2MSFTNGP03.phx.gbl...
> Hi Norman,
> I can do what you suggest on the server, but I cannot do it on a PC
> connected to the same server.
> If I connect from a PC I get a message that either the SQL database does
> not exist, or the username and password is not OK.
> When I type in exactly the same settings on the server it all works OK. (I
> have not got the exact message for you since it is in Dutch)
> So it seems that I am not allowed to connect to the database via ODBC on a
> PC. Is that correct, or is there another way to do this ?
> Arnold
> "Norman Yuan" <NotReal@.NotReal.not> schreef in bericht
> news:e%23zxmswwHHA.4736@.TK2MSFTNGP04.phx.gbl...
>
|||OK, let us see what exactly you ran into:
1. Strat Control Panel->Administrative Tools->Data Sources(ODBC) applet; You
should be OK.
2. Click User DSN or SYSTEM DSN tab (Assume to create a System DSN). You
should be OK.
3. Click "Add Button" -> select "SQL Server" ->Click "Finish" to get you
into "Create a New Data Source to SQL Server" wizard. You should be OK.
4. Enter DSN name, Description and select the SQL Server from dropdown list
(you can also type SQL Server/instance name, too). You should be OK to go to
Next step.
5. Here is your turn to describe what you did on each step for next three
steps that leads you to failure.
"Arnold" <Arnolddegreef@.quicknet.punt.nl> wrote in message
news:eZYofzwwHHA.2040@.TK2MSFTNGP03.phx.gbl...
> Hi Norman,
> I can do what you suggest on the server, but I cannot do it on a PC
> connected to the same server.
> If I connect from a PC I get a message that either the SQL database does
> not exist, or the username and password is not OK.
> When I type in exactly the same settings on the server it all works OK. (I
> have not got the exact message for you since it is in Dutch)
> So it seems that I am not allowed to connect to the database via ODBC on a
> PC. Is that correct, or is there another way to do this ?
> Arnold
> "Norman Yuan" <NotReal@.NotReal.not> schreef in bericht
> news:e%23zxmswwHHA.4736@.TK2MSFTNGP04.phx.gbl...
>
|||Hello Bill, I will explain my challenge a little more:
I would like to connect a visio drawing to sharepoint. In Visio I have a
task flowchart that visually represents the same (project)tasks in
Sharepoint.
Visio has an excelent Database wizzard that perfectly connects the Visio
tasks to lets say Excel or Access, but I need a connection to the Sharepoint
SQL (MSDE) database for it.
Since I do not want to run the Visio application on the server, I need the
connection from a client to the SQL database on the server.
Do you have any more suggestions ?
Thanks in advance,
Arnold
"William (Bill) Vaughn" <billvaRemoveThis@.betav.com> schreef in bericht
news:%23xzhe%23wwHHA.4568@.TK2MSFTNGP03.phx.gbl...
> After Windows 2000 SP2, MSDE was blinded and its ports were shut off. This
> prevents it from being attacked by the slammer virus? worm...-whatever.
> These ports and protocols must be reenabled for the service to be visible
> on the network. Yes, you can connect via ODBC but there are a lot of other
> alternatives that are more efficient. Are you building a .NET application,
> VB6 or what?
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> Visit www.hitchhikerguides.net to get more information on my latest book:
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
> ------
> "Arnold" <Arnolddegreef@.quicknet.punt.nl> wrote in message
> news:eZYofzwwHHA.2040@.TK2MSFTNGP03.phx.gbl...
>
|||Visio uses ODBC connection. You create ODBC connection to SQL Server exactly
the same way (the same wizard) as you do from Control Panel->Administrative
Tools->Data Source(ODBC).
To connect to SQL Server you need to:
1. Make sure the SQL Server can be connected from network. If you are not
sure, ask your network admin/database admin. If you installed the SQL
Server, you need to learn more beofre install it and use it.
2. Know the database name.
3. Ask the network admin/database admin for the SQL Server name, login
credential (if the SQL Server uses Windows security, make sure your windows
account has necessary access permission; if SQL server uses mixed security
mode, you can ask username/password instead). If you installed the SQL
Server, again, a bit more study on SQL Server security before using it.
The Wizard just does that: ask you SQL Server name, login credential, and
then database name. As long as you can supply them correctly, there
shouldn't any problem.
"Arnold" <Arnolddegreef@.quicknet.punt.nl> wrote in message
news:ena0PhywHHA.4228@.TK2MSFTNGP06.phx.gbl...
> Hello Bill, I will explain my challenge a little more:
> I would like to connect a visio drawing to sharepoint. In Visio I have a
> task flowchart that visually represents the same (project)tasks in
> Sharepoint.
> Visio has an excelent Database wizzard that perfectly connects the Visio
> tasks to lets say Excel or Access, but I need a connection to the
> Sharepoint SQL (MSDE) database for it.
> Since I do not want to run the Visio application on the server, I need the
> connection from a client to the SQL database on the server.
> Do you have any more suggestions ?
> Thanks in advance,
> Arnold
>
> "William (Bill) Vaughn" <billvaRemoveThis@.betav.com> schreef in bericht
> news:%23xzhe%23wwHHA.4568@.TK2MSFTNGP03.phx.gbl...
>
|||Hello Norman, thanks for your reply.
My answers are in your reply:
"Norman Yuan" <NotReal@.NotReal.not> schreef in bericht
news:%23xSVlAywHHA.1776@.TK2MSFTNGP03.phx.gbl...
> OK, let us see what exactly you ran into:
> 1. Strat Control Panel->Administrative Tools->Data Sources(ODBC) applet;
> You should be OK.
OK

> 2. Click User DSN or SYSTEM DSN tab (Assume to create a System DSN). You
> should be OK.
No problem

> 3. Click "Add Button" -> select "SQL Server" ->Click "Finish" to get you
> into "Create a New Data Source to SQL Server" wizard. You should be OK.

> 4. Enter DSN name, Description and select the SQL Server from dropdown
> list (you can also type SQL Server/instance name, too). You should be OK
> to go to Next step.
Also no problem. My name is : SERVER-FILE\MICROSOFT##SSEE
Server-File is the name of our Server, and MICROSOFT##SSEE is the standard
name Sharepoint give to the database.

> 5. Here is your turn to describe what you did on each step for next three
> steps that leads you to failure.
I can choose between Windows NT verification, or SQL server verification. If
I choose NT verification, I get this message (not an exact reply, since it
is in Dutch):
Connection failure:
SQL State 01000
SQL Server error 223
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
Connection failure:
SQL State: 08001
SQL Server error 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL-server does not exist, or
the login is refused (someting like that).
The same thing goed for a SQL server verification. I would not know that
username and pass to fill in here. The administrator login does not work
here.
If I do this on Server-file however, I do get a connection.
Arnold
> "Arnold" <Arnolddegreef@.quicknet.punt.nl> wrote in message
> news:eZYofzwwHHA.2040@.TK2MSFTNGP03.phx.gbl...
>
|||Hi Norman,
I am the administratror and have all the rights to my network/database
admin.
I have not installed the server myself. A third IT party did that for us.
They also do not know why I cannot connect directly from a PC. They did not
put in a specific password. It is a standard installation.
Furthermore the SQL server works perfectly OK on the Win 2003 server. We are
using this for more than two years. The only problem I have is making a
connection from ODBC on a PC.
Arnold
"Norman Yuan" <NotReal@.NotReal.not> schreef in bericht
news:uExHC2ywHHA.4308@.TK2MSFTNGP02.phx.gbl...
> Visio uses ODBC connection. You create ODBC connection to SQL Server
> exactly the same way (the same wizard) as you do from Control
> Panel->Administrative Tools->Data Source(ODBC).
> To connect to SQL Server you need to:
> 1. Make sure the SQL Server can be connected from network. If you are not
> sure, ask your network admin/database admin. If you installed the SQL
> Server, you need to learn more beofre install it and use it.
> 2. Know the database name.
> 3. Ask the network admin/database admin for the SQL Server name, login
> credential (if the SQL Server uses Windows security, make sure your
> windows account has necessary access permission; if SQL server uses mixed
> security mode, you can ask username/password instead). If you installed
> the SQL Server, again, a bit more study on SQL Server security before
> using it.
> The Wizard just does that: ask you SQL Server name, login credential, and
> then database name. As long as you can supply them correctly, there
> shouldn't any problem.
>
> "Arnold" <Arnolddegreef@.quicknet.punt.nl> wrote in message
> news:ena0PhywHHA.4228@.TK2MSFTNGP06.phx.gbl...
>
|||"Arnold" <Arnolddegreef@.quicknet.punt.nl> wrote in message
news:%23H%23b1s6wHHA.1168@.TK2MSFTNGP02.phx.gbl...
> Hello Norman, thanks for your reply.
> My answers are in your reply:
> "Norman Yuan" <NotReal@.NotReal.not> schreef in bericht
> news:%23xSVlAywHHA.1776@.TK2MSFTNGP03.phx.gbl...
> OK
>
> No problem
>
> Also no problem. My name is : SERVER-FILE\MICROSOFT##SSEE
> Server-File is the name of our Server, and MICROSOFT##SSEE is the standard
> name Sharepoint give to the database.
>
> I can choose between Windows NT verification, or SQL server verification.
> If I choose NT verification, I get this message (not an exact reply, since
> it is in Dutch):
> Connection failure:
> SQL State 01000
> SQL Server error 223
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
> Connection failure:
> SQL State: 08001
> SQL Server error 17
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL-server does not exist, or
> the login is refused (someting like that).
It is obvious that the user account you used on the client PC, where you
want to create a ODBC connection to SQL Server, does not have access to the
SQL Server. Since you alread passed previous step (i.e. you have selected
the SQL Server name from a list), we are sure the SQL Server DOES exists and
can be seen by the client PC. So, you are facing SQL Server security
problem. No offence, I recommend you study a bit more on SQL Server
security, especially how to create SQL Server login, Database user and map
Windows/Domain user account to logins and users. If you kown a window/domain
user account can access the sql server/database, then use that account to
log on the PC. If you are to create System DSN, you need to use an admin
account.
Most likely, if you log on the PC as domain admin, you would be able to
access the database. Or, you could log on to the server computer, where the
SQL Server sits, as local admin, you should be able to get into the SQL
Server, then you can create necessary SQL Server logins/database users, and
map them to different computer users/groupd on your network/domain. Again,
do the due study before doing so, you do not want to open the SQL Server too
widely to put in risk.

> The same thing goed for a SQL server verification. I would not know that
> username and pass to fill in here. The administrator login does not work
> here.
If you choose SQL Server authentication, you have to make sure the SQL
Server's mixed security mode is enabled (it is disable by default). If not
enable, of course you can not use it. You must know a login's
username/password in pair, which is not the windows username/password,
whether it is admin or not.

> If I do this on Server-file however, I do get a connection.
> Arnold
>
>
sql