31

Note: I've obviously changed the server names and IPs to fictitious ones.

Here's what's going on. I've got a server, which I'm calling MYSERVER, running Microsoft SQL Server Express 2005. Right on this server itself, I've got an ODBC connection set up pointing at itself, and that already works perfectly. I log in using SQL Server Authentication (not Windows authentication), and it's set up like this:

Image of good ODBC connection

Like I said, that one works. But next, I've got another computer which is on a totally different domain/not on the intranet, that needs to access this same SQL Server hosted on MYSERVER. Because it's on a different domain, it doesn't recognize the name "MYSERVER"; I have to point it at the IP address of MYSERVER, which we'll say is 123.456.789.012. But the ODBC connection doesn't seem to work there. I tried setting it up like this:

Image of bad ODBC connection

This doesn't work. When I put in the username and password and press Next, it stalls for a good 10 to 20 seconds, and then finally comes back with the following error:

Connection failed:
SQLState: '01000'
SQL Server Error: 1326
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

If I try the same thing, but change the "server" from 123.456.789.012\SQLEXPRESS to just plain old 123.456.789.012, I get a different error:

Connection failed:
SQLState: '01000'
SQL Server Error: 14
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Invalid Instance()).
Connection failed:
SQLState: '08001'
SQL Server Error: 14
[Microsoft][ODBC SQL Server Driver][DBNETLIB]Invalid connection.

Now I know what you're thinking. You might be thinking, "duh, you probably didn't open the firewall for port 1433, dummy." Except I did, and I verified this, as I can successfully run:

telnet 123.456.789.012 1433

...from the command line all I want. So I'm not sure what to do. I know the SQL Server exists, works, and an ODBC connection can be set up properly; I'm just not sure what it is I've got wrong in my connection settings that's throwing these errors. Based on the latter error I listed, it would seem that it can connect to the server, but simply cannot find the instance (since I didn't specify one that time). So does that mean I just need to use some different syntax to specify the IP along with an instance name? What do I do? Thanks in advance.

Improve this question

11 Answers 11

Reset to default
19

Named pipes and TCIP protocols for SQL server 2005 are disabled by default. Have you enabled them under "SQL server configuration manager"? You can find the protocols in the SQL Server Network configuration and under SQL Native client xx configuration.

The connection on the server itself works thanks to the "Shared memory" protocol.

Improve this answer
3
  • 9
    I'm accepting your answer because it got me on the right track. Under SQL Server Configuration Manager, TCP/IP was already enabled all along under "SQL Server 2005 Network Configuration > Protocols for SQLEXPRESS" and "SQL Native Client Configuration > Client Protocols." Named Pipes was already enabled in the latter. I enabled it in the former, but that didn't seem to help (same error messages). Finally, I switched the ODBC source to use the "SQL Native Client" driver instead of the "SQL Server" driver, and that finally DID work.
    – soapergem
    Nov 9, 2009 at 14:08
  • Thank you SoaperGEM! I never would have thought to have tried SQL Native Client! Worked like a charm!
    – Phillip Senn
    Nov 15, 2012 at 22:02
  • How did u switched the ODBC source to use the "SQL Native Client" driver ?
    – kudlatiger
    Dec 15, 2021 at 6:25
6

Have you enabled the SQL Server Browser service as per How to configure SQL Server 2005 to allow remote connections?:

If you are running SQL Server 2005 by using an instance name and you are not using a specific TCP/IP port number in your connection string, you must enable the SQL Server Browser service to allow for remote connections. For example, SQL Server 2005 Express is installed with a default instance name of Computer Name\SQLEXPRESS.

Improve this answer
2
  • Does connecting to the server\instance on the local use a different mechanism than remotely??
    – Sam
    Nov 6, 2009 at 6:24
  • @Sam see Hakan Winther answer.
    – Sim
    Nov 7, 2009 at 23:45
5

This thread fixed the same error message for me when using Access to connect to a MSSQL 2008 server. MSSQL 2005 and earlier worked fine just using the computer name in the connection string, but I had to change to the connection string in Access to the full form when the instance was upgraded to 2008:

servername\instancename,portnumber

Improve this answer
1
  • 2
    That did it for me, except I didn't need the port number.
    – bgmCoder
    Jan 3, 2013 at 16:51
2

I had this same issue and managed to resolve it by changing the SQL driver to the SQL Native Client driver too. This was weird as in my case this had been working fine for years and then just stopped working. I suspect this is something along the lines of the driver being corrupt, perhaps a reinstal of MDAC might assist, but for the time being I'm happy that it is working at all!

Improve this answer
1

Would it recognize the server name if it were fully qualified? If it were MYSERVER.domain.com? We've had to do that with SQL Server when connecting from a different domain. If you can get the ODBC System DSN to connect when you test the data source, you're making progress. If it doesn't connect in the ODBC setup, you have to fix that first.

Improve this answer
3
  • That's exactly what I'm trying to do--to get the ODBC System DSN to connect in the first place when I test the data source. So I tried your suggestion, trying both MYSERVER.domain.com\SQLEXPRESS and MYSERVER.domain.com, but those both gave me the same errors I listed above, respectively.
    – soapergem
    Nov 6, 2009 at 2:09
  • At a command prompt on the other computer type "nslookup MYSERVER.domain.com" - does that resolve to the right IP address (123.456.789.012) or at all?
    – Sim
    Nov 6, 2009 at 2:46
  • @Sim - Yes, that command does resolve properly.
    – soapergem
    Nov 6, 2009 at 3:07
1

Are you using named pipes or TCP on the ODBC connection that does work? Is TCP enabled?

Improve this answer
1

Make sure you have remote tcp connectios enabled for the SQL server.

Improve this answer
0

Just a wild shot here but what happens if you put a double backslash before the server name or IP address? It just seems to me that it's normally required for most other Windows/Microsoft connections.

Improve this answer
1
  • Trying the double backslash results in the same 1326 error (the first one I listed).
    – soapergem
    Nov 6, 2009 at 3:10
0

I had a similar issue. In my case another sw configures the ODBC setting so i cannot change the driver. I solved my case like this:

  1. Set TCP/IP settings for server to use the port 1433
  2. On the client enter the server address like: 192.168.1.5,1433 (no instance name)

Then it started to work. Client OS: Win7 x64 Driver: sqlsrv32.dll

Improve this answer
0

For what it's worth, I started getting this error on a connection that had been previously working. It turned out that the MSSQL$SQLEXPRESS service had somehow got stopped. Restarting it fixed the problem.

Improve this answer
0

I had this issue as well, and it was as simple as this: the server listed as the primary DNS server for this server was removed, and the app was no longer able to correctly resolve the SQL server's name on the network. Everything immediately started working again when I corrected the local DNS server to a functioning one.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .