Connecting to a named instance of SQL Server 2008

Question

My ASP application connects to the network server where SQL Server 2000 is installed with no problem. The old code that works:

myConn.Open ("Driver={SQL Server};
              Server=myNetwrkServer;
              Database=myDB;
              UID=myID;PWD=myPWD;Trusted_Connection=NO;")

An instance of SQL server 2008 was installed on the same network server. The new code doesn't work:

myConn.Open ("Driver={SQL Server Native Client 10.0};
                      Server=myNetwrkServer\SQLServ2008;
                      Database=myDB;
                      UID=myID;PWD=myPWD;Trusted_Connection="NO";)

Please help!

Answer 1

Named instances require the SQL Server Browser Service to be enabled and started. If this service is not started on your myNetwrkServer machine then the connect from network will fail as they will not be able to resolve the instance name to an actual listening port.

A second problem is that you changed the driver to {SQL Server Native Client 10.0}. This requires that you install the SQL Server 2008 native driver on your ASP machine. There is no reason to change the driver, you should leave the driver to the old {SQL Server} and let OleDB resolve the low level connecting driver for you. By specifying not only the native driver, but even the version number, you are dictating the low level connectivity stack and even if it would work, it would break at when you upgrade to SQL Server 2008 R2. Just leave it at the generic {SQL Server} and let the driver manager figure out the details.

And finally, you need to make sure the login/password is defined on the new server.

As a generic rule, 'new code doesn't work' is never a something you should put in a request for help. Always put how it doesn't work. Do you get an exception, or an error? What exception, what message? Trying to help you doesn't have to be a mystery novel.

Answer 2

You have mismatching quotes near the end of the line.

Should look like this

myConn.Open ("Driver={SQL Server Native Client 10.0};
                      Server=myNetwrkServer\SQLServ2008;
                      Database=myDB;
                      UID=myID;PWD=myPWD;Trusted_Connection=NO;")

Answer 3

Trusted_Connection="NO";)

It looks like surrounding the value there with double quotes would throw things off.

Instead of:

("Driver={SQL Server Native Client 10.0};Server=myNetwrkServer\SQLServ2008;Database=myDB;UID=myID;PWD=myPWD;Trusted_Connection="NO";)

It looks like you should have:

("Driver={SQL Server Native Client 10.0};Server=myNetwrkServer\SQLServ2008;Database=myDB;UID=myID;PWD=myPWD;Trusted_Connection=NO;")

Answer 4

As others have said, the quotes are mismatched. But, you shouldn't need the trusted connection bit. You either use UID= and PWD=, or Trusted_Connection=yes. You don't need all attributes at the same time.

This should work fine:

myConn.Open ("Driver=SQLNCLI10; 
              Server=myNetwrkServer\SQLServ2008; 
              Database=myDB; 
              UID=myID;
              PWD=myPWD;") 

Answer 5

In addition to what Eton B and Remus Rusanu said, you should also check that the Named Pipes protocol is enabled on the SQL Server. To check (on the server):

1. (Programs in the Start menu)
2. (Microsoft SQL Server 2008/Microsoft SQL Server 2008 R2)
3. SQL Server Configuration Manager
4. Expand SQL Server Network Configuration
5. (Select Protocols for ). This will list the protocols and whether they are enabled.

If Named Pipes is not enabled, right-click, Properties, change Enabled to Yes.

Another choice is to force tcp (obviously use similar steps as above to verify that tcp is enabled). You can force tcp by changing your connection string so that you prefix your server name with tcp::

myConn.Open ("Driver={SQL Server};
              Server=tcp:myNetwrkServer;
              Database=myDB;
              UID=myID;PWD=myPWD;Trusted_Connection=NO;")

If you force tcp, also take note that by default your application will access the database on port 1433 which means that port needs to be open on the server if that server is running a firewall.

Answer 6

This is the checklist I go through when the connection to a new SQL Server fails from any network application (some points have already been mentioned):

1. Check if the SQL Server has SQL and Windows authentication enabled (default install option is Windows authentication only)
2. Check if the SQL Server Browser Service is enabled and running (default install option is not activated)
3. Check if TCP/IP connections are enabled for your SQL Server instance (default is usually disabled)
4. Check if the server firewall allows connections to the SQL Server and SQL Server Browser (newly, this is often an issue)
5. Check if the database user defined in your database also exists on the new SQL Server with same login credentials
6. Check if the database user has the according access rights on your database if you moved your database from another server to the new one
7. And last but not least, check your application database configuration if it is really doing what you expect (this should probably go first)