Search This Blog

Tuesday, 17 July 2012

Cannot connect to a SQL Server 2008 on port 1433

I had a problem trying to connect a new SQL Server 2008 R2 Express when setting up an ODBC connection. The problem is all related to the network port that the SQL Server is listening on. This is outlined in the Microsoft Article KB 823938

ODBC Connection


I have setup an ODBC link to the new SQL Server 2008 R2 instance

Connection Failure


When I atttemped to connect to the SQL Server I got this error.

Listening Ports


So next I ran a netstat - na to see if the SQL Server was listening on port 1433 and I could see pretty quick that it was not.

Registry Change


The next step was to change the port the SQL Server instance was using, this is done in the registry in the key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\MSSQLServer\SuperSocketNetLib\Tcp

The SQL Server was using port 51447 which was dynamically assigned and I wanted to use TCP 1433 which is the default for SQL Server.

Client ODBC


Back on the client I have changed the connection port in the ODBC wizard to 1433



Now I am able to connect to the SQL Server and I can see the databases available in the dropdown.

Test Complete


Now I can complete the wizard and I have a connection.

Listening on Port 1433


Back on the SQL Server I used the netstat - na command and this time I could see my server listening on port 1433.

No comments:

Post a Comment