Tuesday, March 8, 2011

Unable to connect Remote SQL server using SSMS

The day started with an issue, I could not able to connect to our new SQL Server 2008 R2, but I could able to connect to it locally.

error

I went to thru the standard check list.

  1. Is SQL Server Browser service is running?
  2. Is SQL server configured for Remote connection?
  3. Is the SQL Server port 1433 (SQL Server), 1434 (SQL Admin console), 4022 (Service Broker), 135 (T-SQL Debug), 2383 (Analysis services), 2382 (SQL Browser) are added in the exclusion list
    Ref: KB968872
  4. Is the server able to ping and available in network.
  5. Is the server able to connect via SQL Authentication.

The last one was the catch, I could able to connect via SQL Auth but on with Windows Auth, so the problem is Windows Auth, It happens when you are trying to use TCP/IP instead of Named pipes / Shared Memory.  In the SQL Login the Network protocol must be Named Pipes / Shared Memory.
For detailed read.

image

Great! with this change I could able to connect to the SQL Server!!

Later I figure out we can change the order of Protocol in Sql Server config Manager as well.

config

No comments:

Post a Comment

Missing Date

I need to check in a table, is the all day data available, here is the handy script to check the same.. DECLARE @startdate DATETIME ...