A while back i wrote a short note on how to connect to sql server (i know, i know, it's not an Oracle post but I'm allowed to do the odd sql server one now and again) This came in useful this week as we had a connectivity issue that just seemed to come out of nowhere. The understanding i got from the earlier post then enabled me to understand and fix the issue quickly.
So the symptoms were - a connection that had been working for a couple of years suddenly stops working and there have been 'no' changes anywhere (heard that one before anyone..?).
A quick look showed that we were getting this from the dotnet application in IIS when it tried to start
Server Error in '/NavigationSetup' Application.
A
network-related or instance-specific error occurred while establishing a
connection to SQL Server. The server was not found or was
not accessible. Verify that the instance name is correct and that SQL
Server is configured to allow remote connections. (provider: SQL Network
Interfaces, error: 26 - Error Locating Server/Instance Specified)
So basically a connection problem of some sort.
The connection string was a simple
connectionString="Data Source=SERVERNAME\NAMEDINSTANCE;Initial Catalog=dbname;User ID=username;Password=password”
Now the first thing to check was username/password even though the error didn't really seem to be saying that - that was fine when tested on the db server itself (always best to rule that out).
Now the App server box had sql management client on so we tried a connection from there and got the exact same error as from dotnet - so this implied some kind of network issue in the two talking to each other.
We checked the basic name resolution and did a telnet test to port 1433 where this instance was running and that was all OK - so what could it be?
Well this is where the original post came in useful. If you notice the connect string we are using a named instance - the connection has to talk to the sql browser service on the db server to be told which port (in this case 1433 the instance is running on). This communication to the browser service uses port 1434 over udp. It turned out that somehow the firewall rules got changed and this was being blocked (as identified by the portqry tool).
OK - so an easy fix right - just get the firewall opened again? Well that's one solution but one that would take a lot of time to organize.
The other solution is to take the browser service out of the process and just tell sql what port things are on. We do this with a slightly modified connect string (see the nice bits in the lurid green colour)
Data Source= tcp:servername\namedinstance,1433;Initial Catalog=dbname;User ID=username;Password=password"
Now when we connect even though we still specify the named instance its essentially redundant - we are explicitly saying connect on port 1433.
Comments
Post a Comment