Solved

SQL Native Client

Posted on 2016-08-13
51
60 Views
Last Modified: 2016-09-13
SQL Native clients stopped connecting to MSS 2008 R2 database from all Windows 7 64-bit clients.

Client/server app uses a UDL file on the clients for connection info. We changed to Ole DB Provider (using same connection info) and they work fine so that's the workaround (and perhaps should be the fix) but my CIO wants to identify the cause.

App has been running for years with this config. No changes to server, app, or config of which we're aware. No apparent changes except Windows updates were installed the night before.

Checked Windows updates for Windows server 2008 (64bit). Don't see anything related. Checked programs on the server and appears an update to Office 2003 Web Components was applied the night before (believe it's originally installed with MSS 2008 and is for Project Web).  I don't see any others that could be related but we also have fixes pushed via other means "Bigfix". Or BES, for example as well as GFI Languatrd). Checked DNS, firewalls., etc even though it is connecting via ole db and thereby probably not related to these things.  Confirmed via MSS Config Manager that ports are enabled. Checked Native files on server and they are an older date (not recently update).  

Error is generic - can't connect to db. Since the ole db driver works, and the problem is with all clients, I assume the issue is specific to the Native on the server or an update that impacted the Native client specifically on all workstations.

Is there a way to see all updates in one place to ensure we haven't missed any MSS updates?  

They are authenting to the server via NTLM (we only support NTLM 2 but we checked and see the NTLM connections) using Wndows authentication (db is integrated/mixed). MSS service account is Local System and has not changed (we know we need to change this to an account with lower privileges but want to resolve this first).

A friend suggested checking the SQL Native on the server. I think he was referring to an update possibly having been applied. But he wasn't specific. Can anyone explain how I do this (assume there is some way besides checking Windows updates)?  

Any ideas what else we can try?
0
Comment
Question by:Marisa Stevenson
  • 26
  • 14
  • 10
  • +1
51 Comments
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41755372
By any chance, do you get a n error number and error state information? If you do, it would be instrumental in the root cause analysis.

If you have a login failed message (error 18456), the error state will point to the root cause (as highlighted here: http://www.sqlservercentral.com/blogs/discussionofsqlserver/2013/04/18/getting-the-lowdown-on-18456-errors/)

The other reasons for the DB not being available could be that the server is going in some sort of a "hibernation" mode, or recent configuration changes on the DB (auto close, etc) are preventing it from responding in a timely manner.
1
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41755452
UDL files make up a connection information definition only working for OleDB providers.

https://msdn.microsoft.com/de-de/library/e38h511e(v=vs.71).aspx

The first line of a UDL file already hints on that: [oledb]

Whenever you thought you were using the SQL Native Client ODBC driver, you didn't. SQL Server Native Client is both available as ODBC and OLEDB and you most probably always did use OLEDB providers.

So what changed lately? You updated ODBC drivers and removed OLDB providers? The last thing would explain the broken connection, for example.

Bye, Olaf.
2
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41755985
Client/server app uses a UDL file on the clients for connection info.
It's that a single UDL file being shared in the network or you have an UDL in each single client?
0
 

Author Comment

by:Marisa Stevenson
ID: 41756496
The UDL file is on the each of the clients -not a single shared file.  

We had it configured to use the SQL Native providers for years.  One morning we came in and no clients could connect. We changed the UDL file to use the Ole DB provider and it worked so we did it on all clients and they all worked with the exception of one which we can't get to connect either way.  Same connection info (server and db name, authentication).

As far as we know nothing changed except some Windows updates that were applied on the server on 8/10. These included an update to Office 2003 Web Components which from what I understand is unrelated. There was also a security fix related to authentication for password changes. We didn't manually change any drivers but it's possible some update was pushed.  We checked the native drivers (I think...best I could determine Basel. Google searches) on the server and they existed with old dates. I would be glad to check these files if someone can tell me specifically what to look for and where.  

I haven't found an error code. It's a generic error indicating "SQL server unavailable or access denied".  Can't access any of the databases on that instance.  It's the only instance (default) on that server and using the default 1433.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41756549
Any chance that SQL Server machine also got updated with any fix or service pack? Or even SQL Server being upgraded to a more higher version?

Did you check for firewall blocking that strict client machine?
1
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41756565
At a customers site I seldom experience that same error, and next day everything is back to normal. So that's only temporary and only network issues, eg a loopback at one router or - less easy to figure out - a larger network segment. That causes network packets (ie the login requests) not arriving at server, but dying out after TTL (time to live) of network packets was decremented to 0. Network contention by massive network usage, eg every client downloading MS updates at the same time, can also cause connections failing. Especially if the SQL Server still is idle that's a good explanation the connection requests never arrived there, so it looks like no problem exists from server side.

Did anything change in the UDL file? Can you post (except credentials) what's the connection string in there?
As I already said you most probably always used OLEDB providers, unless you really took the detour through the ODBC via OLEDB MSDASQL provider.
The technology used - ODBC vs OLEDB - will not make a difference if it's network issues, but obviously if you don't have the necessary client-side drivers or updates occurred there.

Bye, Olaf.
1
 

Author Comment

by:Marisa Stevenson
ID: 41756682
I'll have to get to a client machine to share the UDL.  Do you want screen prints of the properties or via the GUI or can I use notepad or some editor to provide a raw format?

The network appeared fine - we never had a problem pinging the server or accessing it in any other way.  We did see some odd errors in Event Viewer but they were present before we started having this problem and after it was resolved on most machines. It was like clock work - a machine would not connect, and we'd change it's provider and it would connect (with the exception of the one that seems to be stubborn and which can't connect regardless what we do).

The aforementioned Event Viewer errors ( event ID 5157)  indicated packets dropped and connections blocked but we see these logged from the Windows Filtering Platform commonly.

I did notice when I ran 'select * from sys.server_principals' the AD group for these client users displayed a default database that no longer exists.  But I figure that's irrelevant since most members of that group are were connecting, and are connecting once we changed the provider, and since we specify the database and server name in the connection string.  But it made me wonder if some registry setting or some config file might have the old db name in there and if it could be causing problems.  I changed the default db but it didn't seem to make a difference for that one user still having problems.

On this AD group which has rights to the db, when I look at security in SSMS I see "cannot execute as the service principal because the principal "AD Group" does not exist, this type of principal cannot be impersonated or does not exist".  Again, must not make a difference in this case because they are all working with the native client and had worked until the date the problem occurred?
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41756685
If you copy the UDL file and rename the extension of the copy to TXT you can open it in notepad, it's a simple text file in itself, the UDL extension just is associated with the OLEDB equivalent of the ODBC manager to provide a user interface to act on these files, but in the end it mainly contains an OLEDB connection string stating all necessary info about what the UI also shows you.

Bye, Olaf.
1
 

Author Comment

by:Marisa Stevenson
ID: 41756742
Thanks. I'll check out a working and a non-working client and compare, then provide the info.

I just learned that my CIO recently added a separate account for another application to add to this database. It requires the SQL Native client and uses web.config. Different users/clients so don't think it can be related but wanted to provide all the info I have just in case.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41756822
A web.config sounds like that other application is ASP.NET or alike and has its own connection mechanism towards that database. That wouldn't affect any clients, as it happens server side. It could be an interesting detail to know the part of the web.config making up a connection to the database, but even if that would use another provider or driver than your client/server app, this shouldn't affect it, unless there is a change in logins having a side effect to you.
1
 

Author Comment

by:Marisa Stevenson
ID: 41756853
UDL.txt attached.  I browsed the registry on both working and non-working machines and found no discrepancies and appeared to have correct values.  The sqlsrv32.dll and sqlncli.dll on both machines were older unchanged versions.
0
 

Author Comment

by:Marisa Stevenson
ID: 41756855
I'm not sure the udl uploaded.  Let me try again.
UDL.txt
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41756874
This is not making use of SQL Native client, but a rather old OLEDB provider  for SQL Server connection strings, see ConnectionStrings.com

If you want to use SQL Native Client your UDL file should contain Provider=SQLNCLI10 or SQLNCLI10 instead of SQLOLEDB.1, what's eligible also depends on your SQL Server version.

You said you wanted to get the UDL file from a working and non-working client. Are both UDL files equal?

Bye, Olaf.
1
 

Author Comment

by:Marisa Stevenson
ID: 41756907
Yes they are equal. We updated all clients to use the ole db. All except 2 clients work. The rest of the connection info is identical regardless of the provider we select. . I can certainly go and change it to the native client again and provide that if it would be helpful to you. I assume it would be the same except that first line but perhaps it's best to confirm. The two machines that aren't working don't work regardless whether we use ole db or native (when we set up the UDL)
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41756932
You said
SQL Native clients stopped connecting... ... We changed to Ole DB Provider.

That made me think you changed from SQL Native Client ODBC Driver to SQL Native Client OLEDB Provider, now I see what you actually meant with that. What made me think so is, that Ole DB Provider is just the specification of the connector type, there are several providers and the one you use right now is not ideal.

You should rather continue to use the SQL Native Client OleDb provider, as it worked before. As the provider used doesn't make a difference in the nonworking clients it obviously isn't the culprit, you have to find the real reason. One thing I would try is, whether users of the nonworking clients can work when the log in on a working client, ie whether this is related to their Windows account or the workstations.

Bye, Olaf.
0
 

Author Comment

by:Marisa Stevenson
ID: 41756944
It is not related to the account.  We have the same user who is operating fine on one machine, but cannot connect on the 2 machines that are having problems.

To clarify, UDL file was configured to use the SQL Native client for years and all clients were working.  All machines stopped connecting the morning of 8/10.    This is a critical production system, so we had to find a workaround.  We did this by reconfiguring the UDL file to use the Ole DB provider.  This is working for all but two machines, which can't seem to connect regardless whether we configure the UDL with the Native or Ole DB provider.

I don't know whether we have 2 distinct problems (the users who are using the workaround and the 2 machines which can't connect regardless of the provider).
0
 

Author Comment

by:Marisa Stevenson
ID: 41756947
Note, the UDL file is on the clients.  So we reconfigured about 20 machines to use the Ole DB provider as a workaround.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41757041
Stop saying you changed to the Ole DB provider, SQLN11 also is an Ole Db provider, you changed which OleDB provider you use, you changed from SQLNCLI10 or SQLNCLI11 to SQLOLEDB.1

In contrast you didn't change from ODBC to OleDB, you stayed OleDB just using another provider, a more general but older one.

OK, so we know it's not the account, to see whether it's the network or the client itself: What if you move these machines and plug them into the places working? This would check, whether it has to do with the endpoint in the LAN, either the final cable or switch the problematic clients are connected to.

Bye, Olaf.
1
 

Author Comment

by:Marisa Stevenson
ID: 41757078
A new tidbit I just learned. The other app was working using the SQL native client and it stopped working too. Points to something on the server even more now since those clients are different and use a different account. Did I mention I am not a DBA (if that's not obvious). Due to our organizations structure we don't have a dedicated DBA but are going to have one from a supporting office look at this tomorrow so if you think of anything specific he might check please let me know.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41757101
I'm also rather a database app developer than DBA, but I'd use tools like SQL Profiler and also turn on logging both failing and successful logins (Login Auditing).

And as the developer I am I would try to create a repro, a small application just connecting and making a simple query and together with the expert test the real application and that test application live while trying settings.

Bye, Olaf.
1
 

Author Comment

by:Marisa Stevenson
ID: 41757737
I appreciate your clarifying that we are dealing with the Provider.  I had not grasped the difference

We did turn on tracing but nothing significant in the logs that we can see

I'll correct/clarify the problem description. We had  machines using the Native provider that stopped connecting to the db but continued to connect to other network resources.  We changed to the Ole DB provider and were able to connect using this provider on all but two machines.  This is a workaround but we still need to get the Native provider to work. On the two machines that aren't working (perhaps unrelated problem but seems suspicious)  the connectivity test fails when we test it (when we change the provider and re-enter connection info, and run a test without any application involved it fails...sorry if this was unclear).   This is also true when we change the machines that are working (with the ole DB provider) back to Native provider.  

We checked network issues, despite the fact we could connect to the server and other network resources using various tools -
Ports/firewalls, DNS, SPN's.

We tried connecting to a different database as well with no luck. I think it can't connect to the (only/default) instanxe. .

Seems to me we have isolated it to something specific to the Native provider. Are we saying there should be no difference between the Native and Ole DB provider?  I assume there is some difference since what would be the purpose in offering the two different providers - can anyone provide any info explaining it?

And, we may have an additional problem on the two machines that aren't working on either provider. Seems like it would be related to the problem with Native provider on all machines but perhaps not.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41757743
Do you have any MSSQL tools in those 2 machines? If you have try to use those tools (SSMS, sqlcmd, bcp, ...) to connect to the SQL Server instance to see if they can connect.

Did you also check in Event Log for errors?
1
 

Author Comment

by:Marisa Stevenson
ID: 41757745
Olaf - darn it...I can't edit my comment and think I still didn't describe it as well as you did. Basically, we select "SQL Native" as the provider when we set up connectivity and the test connection fails. and.  When we select "SQL Server for Ole DB" the test is successful. (Is that better?)
0
 

Author Comment

by:Marisa Stevenson
ID: 41758373
Now one of the clients that was working stopped. We upgraded the native provider to v10. When it failed we tried to set up an ODBC connection but that test failed too. Error is sqlstate 01000 SQL server error 10060 TCP/IP sockets connection open. Sqlstate 08001. SQL server error 17.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41758927
Going to repeat myself since it seems that you didn't realize my last comment
Do you have any MSSQL tools in those 2 machines? If you have try to use those tools (SSMS, sqlcmd, bcp, ...) to connect to the SQL Server instance to see if they can connect.

 Did you also check in Event Log for errors?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 250 total points
ID: 41758990
You should definitely look into logs. You could also have a CAL issue or a firewall/port issue. Since your connection does not specify a port, you use the default 1433. Is that open for every client? Is that blocked for nonworking clients?

Bye, Olaf.
0
 

Author Comment

by:Marisa Stevenson
ID: 41759178
We have looked at the logs. The only thing we found suspicious was the Windows Filtering reporting messages about connections and packets but it does this on all our servers and was doing it before the problem. If it were a firewall issue wouldn't we have the problem consistently, on all machines, regardless of the provider used? In other words, the machines stopped working and then started working -
A firewall would have consistently blocked
Them. 1433 is open to these machines on the firewall. I don't think those tools are installed on the machines but I think they could be, right? I'll ask our DBA about that.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41759183
Each client machine must have Windows Firewall, right? Any chance of the non working machines have the Windows Firewall enabled by mistake?
1
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 250 total points
ID: 41759218
Indeed you can have multiple firewalls in action, both hard- and software. Windows Firewall is not the only software firewall. What speaks for a software firewall is, that it could change due to any update of Windows, AntiVir, Antimalware... and, well, the error you post is a simple connection problem and firewall is one of the first things to check. Also, whether the server is configured to listen to another port but 1433.

You said
When it failed we tried to set up an ODBC connection but that test failed too
On that workstation it points out a very general problem not only with a driver or provider.

Bye, Olaf.
1
 

Author Comment

by:Marisa Stevenson
ID: 41759407
We checked and used tools to test the software firewall several times. No hardware appliance. But I agree that this sudden impact and intermittency of the problem could be anything.  We originally thought it had to be related to the native provider since using the ole DB provider seemed to fix the problem on all (we thought at the time) but 2 machines.  Hard to tell if the 2 that aren't working are actually related to the same or a different problem, although it seems an odd coincidence. We confirmed that the clients are authenticating via NTLM (2)  instead of Kerberos but we're not sure of the reason. They (those that are connecting) are connecting with named pipes instead of TCP/IP even though TCP/IP comes before named pipes in the order.  We've wondered if our INFOSEC group pushed some security policy that didn't make it to all machines but they claim not.
0
 

Author Comment

by:Marisa Stevenson
ID: 41759412
Victor, will definitely check the firewall on the one machine that has never connected. But the one that connected yesterday and later dropped - that couldn't be a firewall unless something changed within that time frame, right?
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 41759425
Marisa, you're working on an assumption that everything is static and in IT and from my own experience, that's not true at all.
I have seen many things since I started working in IT, since users changing stuffs to administrators reconfiguring things without knowing the impact they're doing. And also some mysterious stuffs as configurations being changed and rolled back without nobody admitting they did it.

So, prepare yourself for anything. Don't discard any option by weird it can be.
You're in much better position than us because you're in the field. We can try to give you some ideas from your reports but at the end you're the one that can do the task. And don't be surprised if tomorrow everything starts to suddenly work properly again. That's not magic. It's just IT :)
1
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41759435
I second that.

You can spare a few checks, ie you don't need to check, whether the SQL Server actually is started as long as there are connections to it from anywhere, but you can't skip any other test just because something worked yesterday.

That's the nature of any system, they have more frequent problems when new and when aged. With software people tend to not think about any wear off, but systems change so often and frequent, that you can't rule out anything you didn't explicitly checked for its current status. CALs for example might simply have expired, could also be a reason on just some clients.

Bye, Olaf.
1
 

Author Comment

by:Marisa Stevenson
ID: 41759995
Not disregarding any ideas and not sure what gave you the impression that we are not considering everything at this point .  Have been in the industry over 30 years in a variety of roles and learned long ago to never use the word "impossible".  We have network engineers, INFOSEC, AD team and DBA's looking at this and I am passing on your suggestions and using your input to logically try to isolate the issue or issues.  

But we do have to use deductive reasoning to isolate a problem and my question about the firewall was genuine because I am trying to understand if I am missing something or if my logic is flawed.  If the machine was connected, doesn't this tell us the ports were open to this static IP and the firewall wasn't blocking anything at that time?

And if it disconnected 10 minutes later, what firewall change could have occurred during that 10 minutes if no one manually changed the machine's firewall (we were discussing the clients firewall).  I suppose a policy could have been pushed - but one that was only pushed to this machine at this time of day  seems unlikely in our very controlled
Environment especially when the CISO states no policies were pushed.  Or am I missing something?

 Not impossible and we will check, but I was asking whether I was missing anything - whether you may know of a reason a firewall may block a minute and not the next, as it might serve as a clue to the other seemingly related issues as well.  Wanted to learn if you saw a flaw in my logic based on facts you may know that I don't know.  

I hope I didn't give the impression that I was asking for suggestions and not using them. It's just that so far, what we've tried hasn't led to anything or we haven't had a chance to try the suggestions yet - but we will. Thank you!
0
 

Author Comment

by:Marisa Stevenson
ID: 41760069
I see that kerberos is suggested as best practice but we are falling back to NTLM. Anyone know why Kerberos is best practice?  Is it just security or can it cause connectivity problems?

We're checking port 88 in case this is an underlying cause. And I guess we need to check SSPI if possible.
0
 

Accepted Solution

by:
Marisa Stevenson earned 0 total points
ID: 41760127
Problem resolved. INFOSEC added the sqlserver.exe as a rule in the server firewall. Now all clients are connecting with SQL Native, authenticating with Kerberos and using TCP connections.

Which doesn't make sense to me because;

1. It all worked until 8/10 as is.
2. It mimics the rule we created for 1433 - so it's configured with an identical scope.
3. From what I've read, it's advised that the executable not be used as a rule since the path can change.

If anyone can explain this I would love to understand. Otherwise, at least it's working. Thanks all!
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41760600
INFOSEC added the sqlserver.exe as a rule in the server firewall.
That might mean that SQL Server doesn't have a static port set so every time SQL Server service restarts it will be listening in a different port. When allowing sqlserver.exe process in the firewall that means to let any request to SQL Server pass through no matter the port is listening. Very usual solution for SQL Server instances using dynamic ports.

From what I've read, it's advised that the executable not be used as a rule since the path can change.
The path isn't added in the rule but the process name only so changing the path won't affect the rule.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41760641
OK, nice.

Vitor explained the conjunction with dynamic ports already. You may check your port setting in SQL Server Configuration Manager.

Also see https://technet.microsoft.com/en-us/library/ms175043(v=sql.110).aspx
It also tells what INFOSEC did, adding SQLSERVR.EXE to a rule, in the case of using dynamic ports. It's set for a specific SQLSERVR.EXE, you could have several instances and indeed the rule is not for any SQLSERVR.EXE, but how could the path change? Installed software doesn't move around...Or do you talk of local instances? Wouldn't make any sense to me, though.

One reason for dynamic ports also is told: A named instance runs on dynamic ports by default, so is that a named instance? That would explain it. It would be typical, if you run several server instances, only one of them can be a default instance all further have to be named.

Bye, Olaf.
1
 

Author Comment

by:Marisa Stevenson
ID: 41761052
Hence, the reason we are all still puzzled:

Configuration Manager showed it configured for port 1433 and more importantly, the logs confirmed it was  listening on port 1433 (open to any IP). Plus the ole provider was connecting using port 1433.  When we run queries we see that the only connection changes are the Kerberos authentication and TCP connections (that were NTLM and Named Pipes before).  No dynamic ports were configured.  (SQL Browser was running but it wasn't open to any IP - just loop back but maybe that's all it requires.)

This is the only (unnamed) instance.

I don't recall where I read about the path being a concern. Don't know if that's likely to change with upgrades perhaps?  Maybe  in cases where one is upgrading from an old 32-bit to 64-bit version or from one platform to another (Express, Standard, Enterprise).

I haven't read the article you posted yet but I will.  I'm happy it's working but it does make us a little nervous when the solution doesn't make sense. There's always a little concern that something still isn't quite right and it could happen again.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41761066
Yes, by your explanation is not making sense. But be some bit there to be discovered so it can explain the behavior.
But the more important thing is that issue disappeared. Let wait and see if won't return (I hope not but since you didn't find the root cause I would keep an eye on it).

Cheers
1
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41761129
The only thing I could add is that besides 1433 also port 1434 is related to MSSQL Server, but mainly only for named instances and the Browser service. By default it is not started and unimportant for a single server.

In larger networks, the Browser service becomes more important for establishing connections and finding the instance, but again mainly for named instances.

The only instance could still be a named instance, but the configuration for port 1433 of course speaks against that.

Bye, Olaf.
1
 

Author Comment

by:Marisa Stevenson
ID: 41761299
I just checked and we still have some NTLM and named pipes connections. Anyway, I was wondering if the issue could be a port SQL server uses other than the one it listens on?  For example, if it uses port 80 or 88 and that rule was changed or deleted inadvertently for some other app. That would explain why the new rule would have fixed things.

I checked Config manager again and no dynamic ports are configured anywhere.  And the logs still say it's listening on 1433. And the clients are not configured for any other port.  Is there a query that shows the port a client is connected to or can I rely on the fact that I see users connected via TCP, and since it's (TCP connections on server) configured for 1433, assume that's how the client connected?
0
 

Author Comment

by:Marisa Stevenson
ID: 41761302
Browser service (port 1434) is running.  But as you noted, shouldn't be used in this case. I wasn't sure about its firewall rule since it was only open for loop back but that would make sense if it doesn't talk to clients directly.
0
 

Author Comment

by:Marisa Stevenson
ID: 41761304
Oh an yes, it is not a named instance. I actually had the DBA confirm that just in case I didn't have a good understanding.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41761437
The browser service does talk to clients directly, if it matters, but it doesn't play any role in conjunction with a single instance. You might turn that service off, it shouldn't hurt. If it was used from any client at all, then it should only know about the single instance anyway.

Just read about the browser service here to get a grip on that, though it's certainly low priority:
https://technet.microsoft.com/en-us/library/ms181087(v=sql.105).aspx

When SQL Server clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Server Browser responds with the TCP/IP port or named pipe of the requested instance. The network library on the client application then completes the connection by sending a request to the server using the port or named pipe of the desired instance.

If it never was reachable to clients, that shouldn't have any impact, because clients "guess" the default port 1433 not knowing anything else. If you set another port, this can also be put into the connection string after server/instance as a suffix ",port-number". So the browser service only is needed in case there are unknown ports of further instances. The Express edition also is a special case using dynamic ports. Since it's more often used locally only and/or for development, this doesn't play a role most of the time, as connections then are not at all made via TCP/IP, so the whole network stack plays no role.

Bye, Olaf.
1
 

Author Comment

by:Marisa Stevenson
ID: 41761446
BTW, our Infosec guy did add the path.  Maybe this was a matter of convenience...I wasn't aware that a path needed to be included.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41762048
SQL Server Browser Service is only needed if you're using Named Pipes protocol instead of TCP. You can only have one by server, no matter how many MSSQL instance the server has and it always listening in UDP 1434.

For port usage confirmation you can see in SQL Server logs. After a restart and while SQL Server is loading you'll find something like the following in the SQL Server log:
Server is listening on [ 'any' <ipv4> 1433].
Server is listening on [ 'any' <ipv6> 1433].
1
 

Author Comment

by:Marisa Stevenson
ID: 41763009
Yes we confirmed 1433 was listening on any.  1434 was just showing the loopback but I don't think it's come into play.  Both applications' clients are still connecting successfully and we still have no explanation.  Oh well.  Hopefully they will continue to run for another 10 years without any problems. :)

Thanks for all your help!
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41784314
Marisa, don't forget to close this question.
Cheers
1
 

Author Comment

by:Marisa Stevenson
ID: 41790240
Just want to close this out.  We are unsure how it was resolved.  The only change of which we're aware is adding sql server (the executable) to the db server firewall scope.  But, this should not have been required since we confirmed the correct (default) ports were added, and had been functioning fine as they were for years.  My guess is perhaps some infosec policy was implemented that caused this to no longer function for some reason.
0
 

Author Closing Comment

by:Marisa Stevenson
ID: 41795661
No specific solution was provided so I included what we think resolved our problem, in hopes that it might help other admins who may run across a similar problem.  I am accepting no points for this, but want to provide Olaf and Vitor the points since they invested a lot of time sharing their knowledge and providing troubleshooting tips that would have led to the solution, had it not been so odd.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now