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

media_1342535534541.png

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

Connection Failure

media_1342535543986.png

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

Listening Ports

media_1342535573106.png

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

media_1342535596629.png

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

media_1342535673165.png

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

Connected

media_1342535684893.png

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

Test Complete

media_1342535696532.png

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

Listening on Port 1433

media_1342535742440.png

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