Home Server Database Administration Solution to: How do I get MS SQL Server 2008 to listen...

Get social!

I recently moved a Microsoft SQL Server to a new computer. Basically, I installed the SQL Server software on the new computer, then backed up the databases on the old server restoring them to the new. Everything worked great while I tested using the associated software on the same (new) computer. As soon as I went over to a client PC, though, the problems started.

On the client PC, I created a new system DSN in ODBC Data Source Administrator. I duplicated the settings, except I used the new computer's server name for the "Server" setting. Clicking "Next" a few times (using all the defaults) gave me the following error (snippet):

SQL Server Error: 17

SQL Server does not exist or access denied.

Solution

Turns out I hadn't turned on "Allow Remote Connections" in SQL Server Management Studio under my database properties (in the "Connections" section). Problem fixed, right. Wrong.

One more setting needed updating (truthfully, I'm not sure the above setting even matters here). After searching around a bit, I discovered that there was a second setting in SQL Server Configuration Manager  under SQL Server Network Configuration -> Protocols for <MY-DATABASE> -> TCP/IP. I needed to enable this option. That should do it!

Nope.

The client still gave me the same error. Finally, I threw a Hail Mary and tried replacing the computer name with the computer's IP address in the ODBC Data Source Admin's "Server" setting. Oorah!

If you follow this advice you'll want to make sure your SQL Server PC IP is set to a static address, otherwise you'll have problems as soon as your DHCP server gets rebooted.

Leave a Reply

Bad Behavior has blocked 45 access attempts in the last 7 days.