SQL server ODBC connection, DSN works, direct connection does not
I'm trying to create a test application that connects to a database.
Unfortunately, I do not have access to the site where this is to be deployed and I don't have access to the database so I'm having to send it to someone else to test.
I'm also not 100% sure if the database is SQL Server 2000 or 2005, although I'm pretty sure it's 2005.
This is being done in .NET 2.0 (C#)
The initial tests were successful using a system DSN that's already set up and an OdbcConnection.
The connection string being used was
However we can't seem to get it to connect using a direct connection.
When using this connection string:
We get the error:
And if we try:
We get the error:
So, what am I doing wrong with the connection string?
Or should I be using something other than OdbcConnection and using a completely different connection string, perhaps one that specifies the Provider?
Unfortunately, I do not have access to the site where this is to be deployed and I don't have access to the database so I'm having to send it to someone else to test.
I'm also not 100% sure if the database is SQL Server 2000 or 2005, although I'm pretty sure it's 2005.
This is being done in .NET 2.0 (C#)
The initial tests were successful using a system DSN that's already set up and an OdbcConnection.
OdbcConnection dbConn = new OdbcConnection(ConnectionStringTextBox.Text)The connection string being used was
Dsn=dsnname;persist security info=False;
However we can't seem to get it to connect using a direct connection.
When using this connection string:
Driver={SQL Server};Server=sqlServerInstance;Database=dbName;Persist Security Info=False;Trusted_Connection=Yes;
We get the error:
ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
ERROR [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
ERROR [01S00] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute
And if we try:
Driver={SQL Native Client};Server=sqlServerInstance;Database=dbName; Trusted_Connection=yes;
We get the error:
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
So, what am I doing wrong with the connection string?
Or should I be using something other than OdbcConnection and using a completely different connection string, perhaps one that specifies the Provider?
ASKER
I've already tried that and I get the second error message I mentioned.
Hi capsoftuk,
Have you tried this reference site yet?
http://www.connectionstrings.com/
Since you think it might be SQL 2005 - check this section out for alternative connection string notations:
http://www.connectionstrings.com/sql-server-2005
The first "invalid connection string attribute" error points to possible malformed connection string.
Also, are you using the named-instance of the SQL server as the Server attribute value or the IP address? The hosting company will need to check that Named Pipes and/or TCP/IP is enabled under the SQL SErver Network Configuration -> Protocols for named-instance on the server.
HTH
Deems
Have you tried this reference site yet?
http://www.connectionstrings.com/
Since you think it might be SQL 2005 - check this section out for alternative connection string notations:
http://www.connectionstrings.com/sql-server-2005
The first "invalid connection string attribute" error points to possible malformed connection string.
Also, are you using the named-instance of the SQL server as the Server attribute value or the IP address? The hosting company will need to check that Named Pipes and/or TCP/IP is enabled under the SQL SErver Network Configuration -> Protocols for named-instance on the server.
HTH
Deems
ASKER
Yes I'm familiar with connectionstrings.com and took the above connection strings from it.
I'm using the name of the Sql Server instance rather than the IP address.
Since the DSN works fine using the named instance shouldn't that imply that Named pipes or TCP/IP is already enabled?
And for the invalid connection string bit, could it have been the Persist security info attribute that's invalid?
I've asked the tester to try it without this already but I haven't heard back from them yet.
I'm using the name of the Sql Server instance rather than the IP address.
Since the DSN works fine using the named instance shouldn't that imply that Named pipes or TCP/IP is already enabled?
And for the invalid connection string bit, could it have been the Persist security info attribute that's invalid?
I've asked the tester to try it without this already but I haven't heard back from them yet.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
can you please show the DSN configuration?
If named pipes is not enabled as a protocol for the server - and the DSN is configured (as angellll requested) with IP address - then that is the likely reason that your connection string does not work with the instance name but with the DSN string.
If the DSN configuration is using the named-instance and that's the same named instance you attempted to use then there's potentially another problem.
If you are using a trusted connection you may be required to include the following parameter in your connection string:
Integrated Security=SSPI;
HTH
Deems
If the DSN configuration is using the named-instance and that's the same named instance you attempted to use then there's potentially another problem.
If you are using a trusted connection you may be required to include the following parameter in your connection string:
Integrated Security=SSPI;
HTH
Deems
ASKER
"If the DSN configuration is using the named-instance and that's the same named instance you attempted to use then there's potentially another problem."
This is the case, I'm using exactly the same server and database details in the connection string as the DSN uses, no ip address just named instance and windows authentication.
In addition I had asked my tester to try these connection strings
I'll see about getting the DSN settings up but they're completely standard, named sql server (not ip), Windows NT authentication ticked, Connect to SQL Server to obtain default settings for the additional configuration options ticked, Default database set to the database I want.
This is the case, I'm using exactly the same server and database details in the connection string as the DSN uses, no ip address just named instance and windows authentication.
In addition I had asked my tester to try these connection strings
- Driver={SQL Server};Server=servername;Database=dbname;Trusted_Connection=Yes;
- Driver={SQL Server};Server=servername;Database=dbname;Integrated Security=SSPI;
I'll see about getting the DSN settings up but they're completely standard, named sql server (not ip), Windows NT authentication ticked, Connect to SQL Server to obtain default settings for the additional configuration options ticked, Default database set to the database I want.
ASKER
There may be an issue with it using a non-standard port so I'm going to try a connection string specifying port tomorrow and see what happens.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Driver={SQL Native Client}
http://www.connectionstrings.com
Lee