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.
I went to thru the standard check list.
- Is SQL Server Browser service is running?
- Is SQL server configured for Remote connection?
- 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
- Is the server able to ping and available in network.
- 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.
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.