July 9, 2010 at 1:10 pm
I have a third party app that's had intermittent connection problems and I think the issue is the that sql instance is using dynamic ports and the SQL Browser service was not running. I'd like some help confirming my diagnosis and advice about how to resolve it.
The application's fat clients use an ODBC data source (System DSN) to connect to the database. They all use a shared SQL login for credentials.
The symptoms:
- The error message displayed to the clients is the basic "connection refused or server not found" message.
- Two clients with the same configuration; one can connect, the other one can't.
- I created an ODBC connection on my pc (w/out the client installed) and it could connect to the db just fine; but when I tried to connect from the same machine using SSMS, I couldn't.
- When I started the SQL Browser service, all the clients that couldn't connect before are now able to connect.
- When I looked at the log, these show up as "password mismatch" login failures, although I'm not really sure why that should be the case. For example:
Login failed for user 'AppSQLLogin'. [CLIENT: 10.x.x.x]
Error: 18456, Severity: 14, State: 8.
Server details:
- The database is a named instance, 2005 SqlExpress (9.0.4035), i.e. myserver\sqlexpress.
- TCP/IP is enabled, Named Pipes are not.
- The startup sequence of the most recent log file includes this message: "Server is listening on [ 'any' <ipv4> 3625]."
- Under SQL Server Configuration Manager --> SQL Server 2005 Network Configuration --> Protocols for SQLEXpress --> TCP/IP --> Properties dialog --> IP Addresses tab, the property values are like so:
[font="Courier New"]IP1
Active = Yes
Enabled = No
IP Address = [the IP Addres of the physical server]
TCP Dynamic Ports = 0
TCP Port = [blank]
IP2
Active = Yes
Enabled = No
IP Address = 127.0.0.1
TCP Dynamic Ports = 0
TCP Port = [blank]
IPAll
TCP Dynamic Ports = 3625
TCP Port = [blank][/font]
So should I just leave the SQL Browser service running? I typically don't for our other SQL instances, but they are all default instances (of Standard Edition), so they presumably don't need the browser service, correct?
Or should I stop the browser service and figure out how to set this instance to use a static port? And if so, can I set a named instance to use the standard port 1433, or would I have to change all the ODBC connections to look for a different, but still specific, port?
Are there reasons to prefer one approach over the other? Thanks,
--MIJ
July 9, 2010 at 3:12 pm
One reason I've set our named instances to specific ports is our firewall rules. We want control over over which machines connect to our databases, even if it means entering all those IPs into the firewall rule scope.
For connecting thru an ODBC connection, you can add the port to the server name:
<server name>\<instance name>,<port number>
Notice the comma after the instance name.
HTH
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
July 12, 2010 at 1:37 pm
Dan H. (7/9/2010)
One reason I've set our named instances to specific ports is our firewall rules. We want control over over which machines connect to our databases, even if it means entering all those IPs into the firewall rule scope.
So if I understand correctly, you're saying that using a specific non-standard port gives you an added layer of security.
But I'd rather not reconfigure all the client ODBC connection strings to include a port number, so I guess the root of my question is whether or not there's a significant difference between using the default static port (1433), vs. using dynamic ports and SQL Browser service.
Since I only have one instance on the server it seems like I could choose either option, but I don't know of any compelling reason to pick one over the other. If there's no real difference, that would be good to know, as well.
July 12, 2010 at 1:51 pm
We too have used static ports for firewall purposes.
If you are only using the default instance and default port, you leave yourself at greater risk because 1433 is a well known port to attack.
You could leave the browser service running, but also change to a different port. This would increase security while not requiring a change to all odbc settings.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 12, 2010 at 2:04 pm
sqlbrowser only translates instance name to port number.
So, if you don't want to specify port number with your connections, you should activate sqlbrowser.
You'll have to open the udp port for browser (UDP port 1434) as well as the tcp port of your instance. (can be anything, but I prefer a fixed port number over a dynamic one, just because of the FW settings)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 12, 2010 at 3:40 pm
Thanks for the advice, folks; I think I understand my options a little better now (I hadn't considered a static port w/ SQL Browser, for example) so that should help me as I continue reading and researching.
It sounds like opening ports in the firewall has been the main consideration for most people; that hasn't been an issue for me, but it does make sense as one of the factors involved in deciding how to configure remote connections, so that helps answer my question.
July 12, 2010 at 3:55 pm
This is actually a really important and serious question, and here is some more for you to consider.
Disabled the SQL Browser also adds a layer of security. The browser does a bit more than convert instance name into port number, it also makes it possible to find the server on the network by "broadcasting" its existence.
Try this:
From the command prompt >osql –L This will return all SQL servers "broadcasting" on your network.
Pick a test server from the list, login and STOP the SQL Browser, then run the OSQL -L command again.
You should find the selected server is now missing from the list.
i.e. If someone want's to get you your SQL and is on your network, they can use this to find all your SQL servers.
If you have xp_cmdexec enabled and a sloppy application, a good hacker will be able to do it with SQL Code injection, and you just told them every SQL Server instance in your environment.
I STRONGLY recommend you DISABLE the SQL Browser and set all your SQL Instances to use a fixed port number, nice and high.
The issue of the ODBC is minor, you can create an ODBC on you workstation and use something like Alteris to distribute it to relevant workstations. Make the changes in the evening, and in the morning no one will even know it was done.
Sorry, security out weighs the inconvenience of fixing a few ODBCs.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
July 12, 2010 at 4:06 pm
Leo.Miller (7/12/2010)
Sorry, security out weighs the inconvenience of fixing a few ODBCs.
Agreed!!
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 12, 2010 at 4:06 pm
MIJ (7/12/2010)
Thanks for the advice, folks;
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 12, 2010 at 5:06 pm
Leo.Miller (7/12/2010)
Disabled the SQL Browser also adds a layer of security. The browser does a bit more than convert instance name into port number, it also makes it possible to find the server on the network by "broadcasting" its existence.Try this:
From the command prompt >osql –L This will return all SQL servers "broadcasting" on your network.
....
I STRONGLY recommend you DISABLE the SQL Browser and set all your SQL Instances to use a fixed port number, nice and high.
Leo, thanks for bringing this up, because I think what was in the back of my mind when I was originally wondering about this was that it seemed like there might be a reason that SQL Browser was usually stopped.
However, there must be more than just the SQL Browser service that determines whether or not a particular instance is "broadcasting" (or maybe the -L argument looks at more than just the browser service?). When I ran "osql -L" it found several servers for which the SQL Browser service was not only stopped, but disabled, so I guess that's something else for me to think about.
But w/ respect to changing the fixed port number to somethign "nice and high" (rather than the default, presumably) isn't that sort of "security by obscurity"? I mean that as an honest question (I'm an accidental DBA, so please pardon my ignorance). It seems like switching port numbers just adds another step (i.e. scanning the ports) to what an attacker would have to do to attack your system, but I don't see how it would make it any more secure once the port has been determined. I get that that's a bit of a deterrent in itself, so I can see the argument for it as part of a set of best practices, but am I correctly understanding it's purpose, or are there other benefits to changing the port?
July 12, 2010 at 5:34 pm
MIJ (7/12/2010)
But w/ respect to changing the fixed port number to somethign "nice and high" (rather than the default, presumably) isn't that sort of "security by obscurity"? I mean that as an honest question (I'm an accidental DBA, so please pardon my ignorance). It seems like switching port numbers just adds another step (i.e. scanning the ports) to what an attacker would have to do to attack your system, but I don't see how it would make it any more secure once the port has been determined. I get that that's a bit of a deterrent in itself, so I can see the argument for it as part of a set of best practices, but am I correctly understanding it's purpose, or are there other benefits to changing the port?
A good question, and I don't have a convincing answer, except to say if you put the port number in an obscure, high range it takes that much longer to scan ports, and starts raising the risk of being detected.
There are a few basic assumptions I understand hackers make when they begin, and these are things like: The system isn't secure, the port numbers are standard or close to standard, paswords are simple, often words or words with letter substitution and a few others. Our aim is to move as far away from these as possible.
If a hacker wants to scan the ports, why let him get it right after his second attempt. If he has to scan to a high range he may be detected or give up before he finds the right one.
I don't have a problem with adding "security by obscurity" on top of best practice.
I'm not sure why your servers with the Browser disabled are showing up with osql -L, I'd have to look into it. One possibility is there are DNS's configured or you have a local ODBC defined. Not sure if these could affect it. Maybe there is some one out there who has an answer. (Gail any comments?)
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
July 12, 2010 at 5:57 pm
The servers will continue to show up despite disabling SQL Browser. SQL Browser does not control the default instance and thus an osql -L scan will still find SQL server on the default UDP port. Unfortunately you cannot disable or change the UDP port.
Your best defense there is to use a Firewall that segments the SQL Servers from the rest of the network and has explicit permissions on those ports as to who can access or not.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 12, 2010 at 7:10 pm
CirquedeSQLeil (7/12/2010)
The servers will continue to show up despite disabling SQL Browser. SQL Browser does not control the default instance and thus an osql -L scan will still find SQL server on the default UDP port. Unfortunately you cannot disable or change the UDP port.Your best defense there is to use a Firewall that segments the SQL Servers from the rest of the network and has explicit permissions on those ports as to who can access or not.
Thanks Jason, I knew there would be a good reason.
There is of course another work around for this. Only install named instances. There is nothing that says you must have a default instance.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
July 12, 2010 at 7:57 pm
Leo.Miller (7/12/2010)
CirquedeSQLeil (7/12/2010)
The servers will continue to show up despite disabling SQL Browser. SQL Browser does not control the default instance and thus an osql -L scan will still find SQL server on the default UDP port. Unfortunately you cannot disable or change the UDP port.Your best defense there is to use a Firewall that segments the SQL Servers from the rest of the network and has explicit permissions on those ports as to who can access or not.
Thanks Jason, I knew there would be a good reason.
There is of course another work around for this. Only install named instances. There is nothing that says you must have a default instance.
Cheers
Leo
That is true. Unfortunately there will still be a UDP 1434 still read with every SQL install. With the named instances, you achieve Obfuscation of your instances but the server can still be discovered. The attacker would have to work a little harder to find what instances and connections were there though.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 12, 2010 at 8:06 pm
That is true. Unfortunately there will still be a UDP 1434 still read with every SQL install. With the named instances, you achieve Obfuscation of your instances but the server can still be discovered. The attacker would have to work a little harder to find what instances and connections were there though.
I'm not sure I can agree with this. On the server I did my testing I only have 2 named instances, and no default instance. The osql -L search doesn't return any details on SQL on that server. In essence any hacker would now have to resort to the next level of attack to try discover the server.
I know nothing is fool proof, but that is because fools are so ingenious.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply