palim
asked on
VB & SQL Server
Hi folks,
I'm trying to use ODBC to connect to my SQL Server but I'm getting error messages from the ODBC driver.
I have 6 PCs in the development environment. Out of these, 2 can run without any problems. The other 4 have problems connecting to the server.
1. Cannot connect to server. These PCs initially couldn't and we resorted to upgrading the SQL Server ODBC driver to version 3.5++ and it worked after that but yesterday we couldn't connect again. I doubt its the driver because the two PCs that are working fine are using older versions of the driver (version 2.65++). The error message for this situation is:
Connection failed:
SQLState:'01000'
SQL Server Error: 5
[Microsoft][ODBC SQL Server Driver][dbnmpntw]Connectio
Connection failed:
SQLState:'08001'
SQL Server Error: 2
[Microsoft][ODBC SQL Server Driver][dbnmpntw]Access Denied.
2. Specified SQL Server Not Found. This PC was not upgraded at all (The ODBC Driver) It is identical with one of the PCs that's running fine in terms of ODBC setup. The SQL Server ODBC driver version is identical to the two PCs that's running fine.
However, when we run the application, the SQL Server Login window will appear requesting for User and Password although it has been included as parameters in the OpenConnection statement. (I assume it's because it assumes that our password is incorrect, although it is correct). When I re-enter the items requested, I will get the following message:
Connection failed:
SQLState:'01000'
SQL Server Error: 53
[Microsoft][ODBC SQL Server Driver][DBNMPNTW]Connectio
Connection failed:
SQLState:'08001'
SQL Server Error: 6
[Microsoft][ODBC SQL Server Driver][DBNMPNTW]Specified
All the PCs have the same configuration in terms of ODBC so I can't figure out why I'm getting different result on different terminals.
I've tried everything I can think of and I still can't come up with anything. Any suggestions would be deeply appreciated. Please help me.
Thanks in advance.
palim
I'm trying to use ODBC to connect to my SQL Server but I'm getting error messages from the ODBC driver.
I have 6 PCs in the development environment. Out of these, 2 can run without any problems. The other 4 have problems connecting to the server.
1. Cannot connect to server. These PCs initially couldn't and we resorted to upgrading the SQL Server ODBC driver to version 3.5++ and it worked after that but yesterday we couldn't connect again. I doubt its the driver because the two PCs that are working fine are using older versions of the driver (version 2.65++). The error message for this situation is:
Connection failed:
SQLState:'01000'
SQL Server Error: 5
[Microsoft][ODBC SQL Server Driver][dbnmpntw]Connectio
Connection failed:
SQLState:'08001'
SQL Server Error: 2
[Microsoft][ODBC SQL Server Driver][dbnmpntw]Access Denied.
2. Specified SQL Server Not Found. This PC was not upgraded at all (The ODBC Driver) It is identical with one of the PCs that's running fine in terms of ODBC setup. The SQL Server ODBC driver version is identical to the two PCs that's running fine.
However, when we run the application, the SQL Server Login window will appear requesting for User and Password although it has been included as parameters in the OpenConnection statement. (I assume it's because it assumes that our password is incorrect, although it is correct). When I re-enter the items requested, I will get the following message:
Connection failed:
SQLState:'01000'
SQL Server Error: 53
[Microsoft][ODBC SQL Server Driver][DBNMPNTW]Connectio
Connection failed:
SQLState:'08001'
SQL Server Error: 6
[Microsoft][ODBC SQL Server Driver][DBNMPNTW]Specified
All the PCs have the same configuration in terms of ODBC so I can't figure out why I'm getting different result on different terminals.
I've tried everything I can think of and I still can't come up with anything. Any suggestions would be deeply appreciated. Please help me.
Thanks in advance.
palim
Hello Palim,
You should still get the latest ODBC/MDAC drivers from MSDN. They have them for each OS (95/98, NTw, NTs.) Use version MDACv2.0 as the asynchronous queries don't leak memory any more!
[ http://www.microsoft.com/data/mdac2.htm ]
After running the setups, email me back at jhowell@cyberhighway.net and we'll step thru the diagnostics required for the connection. I agree with dabellei that you should check the permissions under SQL Server for the login used. Also try running matching queries from iSQL/w as the ODBC connection will be exercised as opposed to in Enterprise Manager. You will see great differences/improvements in the dialogs for the ODBC Administrator32. Here we will test everything and KNOW what the issue(s) is/are.
Jason
You should still get the latest ODBC/MDAC drivers from MSDN. They have them for each OS (95/98, NTw, NTs.) Use version MDACv2.0 as the asynchronous queries don't leak memory any more!
[ http://www.microsoft.com/data/mdac2.htm ]
After running the setups, email me back at jhowell@cyberhighway.net and we'll step thru the diagnostics required for the connection. I agree with dabellei that you should check the permissions under SQL Server for the login used. Also try running matching queries from iSQL/w as the ODBC connection will be exercised as opposed to in Enterprise Manager. You will see great differences/improvements in the dialogs for the ODBC Administrator32. Here we will test everything and KNOW what the issue(s) is/are.
Jason
ASKER
Hi dabellei,
Firstly, thanks for your suggestion. I've tried removing the anti virus but it's still giving me the same problem. With regard to the security, the user that I'm using to access SQL does exist. Initially, I even used the sa ID to access the database through ODBC from the client but it still doesn't work.
I've actually assigned the table to the user as well and it's still the same. As for connection type, I'm using the SQL Server authentication type. For the User Name & Password, I'm using one that already exists in the SQL Server with the rights assigned to the particular DB.
The particular user does not have access to master, tempdb and the other system databases... I tried giving the user access to these tables but it doesn't help.
I've also tried installing the SQL Client software and it still gives me the same problem.
Any other possibilities??
Puay Aun
Firstly, thanks for your suggestion. I've tried removing the anti virus but it's still giving me the same problem. With regard to the security, the user that I'm using to access SQL does exist. Initially, I even used the sa ID to access the database through ODBC from the client but it still doesn't work.
I've actually assigned the table to the user as well and it's still the same. As for connection type, I'm using the SQL Server authentication type. For the User Name & Password, I'm using one that already exists in the SQL Server with the rights assigned to the particular DB.
The particular user does not have access to master, tempdb and the other system databases... I tried giving the user access to these tables but it doesn't help.
I've also tried installing the SQL Client software and it still gives me the same problem.
Any other possibilities??
Puay Aun
Are you using a trial version of SqlServer? If you are, probably you have a maximum number of licences using at same time.
ASKER
Nope! It's a licensed version and I actually tried increasing the license but it still hit the problem!
I developed an application on VB 5 and while deploying it found the same problem you are facing. I did the following to get over it.
Installed the ODBC from VB diskettes on the machines. It solved the problems on most m/c however two m/c still were giving the problem. To rectify the problem I went to there Network Neighbourhood and checked if the user could actually see the server physically. one could and one could not. However both did not have permission to work on the server. I got in touch with the network guys got them to change the permission. Everybody is fine now.
To find out if they can see the server share a directory on the SQL Server m/c and check if the user can access it.
Hope this help.
Manish77
Installed the ODBC from VB diskettes on the machines. It solved the problems on most m/c however two m/c still were giving the problem. To rectify the problem I went to there Network Neighbourhood and checked if the user could actually see the server physically. one could and one could not. However both did not have permission to work on the server. I got in touch with the network guys got them to change the permission. Everybody is fine now.
To find out if they can see the server share a directory on the SQL Server m/c and check if the user can access it.
Hope this help.
Manish77
ASKER
Hi Manish77,
Yeah they can see the machine. So I don't think it was the access problem.
Anyway, from what a few people were telling me about the protocol, I decided to take a look. I decided to run the setup to see if there was anything I could change and found the protocol setting where you decide which ones you want to include but almost all of them were included except Banyan Vines, AppleTalk and Multi Protocol.
Anyway, I didn't change it but after that one of the PCs that was working could not connect any more. I took a look at it, took a shot and changed the default protocol to TCP/IP from Named Pipes and it's now working. All the other PCs are also working now and they're running TCP/IP.
The thing is, I didn't change a thing on the server and one of the PCs that weren't working yesterday with TCP/IP is working now.
Anyway, thanks for all your help and suggestions the past few days.
Yeah they can see the machine. So I don't think it was the access problem.
Anyway, from what a few people were telling me about the protocol, I decided to take a look. I decided to run the setup to see if there was anything I could change and found the protocol setting where you decide which ones you want to include but almost all of them were included except Banyan Vines, AppleTalk and Multi Protocol.
Anyway, I didn't change it but after that one of the PCs that was working could not connect any more. I took a look at it, took a shot and changed the default protocol to TCP/IP from Named Pipes and it's now working. All the other PCs are also working now and they're running TCP/IP.
The thing is, I didn't change a thing on the server and one of the PCs that weren't working yesterday with TCP/IP is working now.
Anyway, thanks for all your help and suggestions the past few days.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ex.: Anti-virus software on some PC we cold not connect to sql or it was very long to get the connection. try to take out everything from memory make a clean boot to see if that's not part of the problem.
2. What kind of security are you using on SQL. verify that every body has acces to the SQL.
3. Try to install the client software on the PC to see if you can see the SQL server with those tools, it might be only the network protocol your using.
try those and let me know the result