[RESOLVED] Problem Connecting to SQL Server Express

[Shaggy Hiker]

I have SQL Server Express running on Win8.1, and am trying to connect to it over a home network on a computer running Win7. I've done this before with two other computers on the system where the SQL Server was running on a different computer running XP. It looks like I have SQL Server 2005 Express running on the Win7 system, though I had forgotten about that. I probably put it on there because I wanted SQL Server Management Studio, but that's life.

The Win7 system sees the Win8 computer, and I can share files and the like between the two, but I haven't been able to connect to the Win8 SQL Server. I have a program running this code:

 Dim dSourceInstanct = SqlDataSourceEnumerator.Instance
 Dim dt As DataTable = dSourceInstanct.GetDataSources
 
For Each dr As DataRow In dt.Rows

This allows me to see all the DBs available on the network. Both the Win8 and Win7 systems have this code on them, so I can check what is visible on either one by looking at the rows returned. Both systems see their local DB, and with the XP system running, both computers can see the DB version over on the XP system. However, neither the Win7 or Win8 can see the DBs on the other one. The fact that the Win8 system can't see the Win7 DB didn't bother me any, since I really don't care about that. However, I really need the Win7 system to see the Win8, and I'm not having any luck figuring out what is going on.

When I installed the XP system, I did essentially nothing to configure it. If there was any time spent configuring that at all, I don't remember it, so it is probably in the default configuration.

A bit of exploration shows that I need to have SQL Server installed to accept remote connections, which the Win8 DB had set by default. I then found that the TCP protocol was disabled on the Win8 DB, so I enabled that and restarted the service. That had no impact, so the next step was to change the ports from Dynamic Ports to a fixed 1433 port. That, too, had no impact. I'm not sure what to try next. It's a bit frustrating to have this happen without effort the first time and not be able to make it work at all the next time I try it.

One thing that I note, which may have some bearing on the subject, is that when I go into SQL Server Management Studio on the Win7 system, browse for servers, and look at Network Servers, I see the XP system, I see the local system, but I don't see the Win8 system. That kind of suggests that I have something set up wrong on the Win8 system.

[techgnome]

My first thought was that the Allow Remote Connections needed to be turned on... but it look like you took care of that. For some reason I want to say the problem is related to SQL Express being on-demand ... it only kicks on when there is a connection request. The rest of the time, the service is off, which might be why you're not seeing it in your code and might be why you don't see it when browsing for servers. In short: it's not broadcasting as a server because technically it isn't. SQL Express was for local development and usage originally. But I don't know if I'm remembering that right or not.

-tg

[dee-u]

How about the firewall, have you tried looking into it if it is preventing the connection?

[NeedSomeAnswers]

So a Few questions Shaggy?

Firstly have you checked you Firewall? - Win8 puts it on by default i think

What version of SQL Server Express have you installed?

Have you installed any Service Packs?

And can you connect locally? As in using the same details your program is using can you use them to connect to your local instance through management studio?

(i only ask as i have had the situation when locally i have been successfully connecting using Windows authentication, but i was using SQL Server authentication through my app and my login was incorrect.)

[Shaggy Hiker]

The firewall is a pretty obvious answer, and one that I got into a bit. One thing I would note is that both Win8 and Win7 systems see the SQL Server Express that was installed on the XP system, and I didn't have to tweak any firewall settings on any of the systems to get that. However, I did go into the firewall settings on the Win8 system and explicitly open port 1433, based on some MSDN document. Based on the same document, I also switched the SQL Server Express on the Win8 system from dynamic ports to 1433. Therefore, I don't see how a firewall issue could be the problem here. If it was, then why can I see and connect to the XP instance?

All systems can see/connect to their local SQL Server Express instances. The code noted will show the local instance, and will show the XP instance.

After writing that, and a bunch more, I decided to try a few more tests before writing anything else...and the situation became even more puzzling.

First off, I started up the XP system to have a look at versions. It appears that the versions are as follows:

Win7: SQL Server Express 2005 (this amazes me).
XP: SQL Server Express 2008
Win8.1: SQL Server Express 2008 R2

I then decided to see whether or not the UDP system was working in the code (the DB location is pinged out by the code, as might be expected by a few of you who also participated in a different thread on that subject). When I first ran the test of the UDP system, I paused to see which DBs the Win7 and Win8 saw. To my amazement, Win8 saw the Win7 DBs. I hadn't altered ANYTHING since last night, with regard to the SQL Server settings. Win7 still didn't see Win8, though. Both saw XP and local, as before. I then made a pair of unrelated changes to the code to move reading a GUID from Settings earlier in the code (so that the UDP messages had the right IDs). The UDP messages came through just fine, so the Win7 and Win8 were having no problems communicating over the network using UDP. I then went back to confirm which DBs the two saw, and Win8 was back to not seeing Win7.

So, I essentially ran the same test over and over with breakpoints in different places, but without altering the SQL Server settings in any way (or even looking at them). Briefly, Win8 saw Win7 DBs, then stopped seeing them. The only difference between when it saw them and when it didn't appears to have been possibly related to the fact that both systems were running the code, at the time, though I haven't been able to reproduce the result.

Perhaps there is something about the discovery process used by SQLDataSourceEnumerator such that if one runs at the same time that another is running, the one saw the DBs on the other system. Perhaps it opens some port that is otherwise closed? Perhaps the fact that Win8 saw Win7 was unrelated to the code, which seems reasonable, but if so....then why did it see it?

If this is a firewall issue, then how come both systems can see the XP system? I'm sure that I never explicitly opened anything in the firewall for that system. I didn't even know the firewall would care on a LAN.

[Shaggy Hiker]

And can you connect locally? As in using the same details your program is using can you use them to connect to your local instance through management studio?

(i only ask as i have had the situation when locally i have been successfully connecting using Windows authentication, but i was using SQL Server authentication through my app and my login was incorrect.)

Let me ask you a question back: I am working under the assumption that the message I would get in Management Studio would be different between Finding the DB, but being unable to connect vs Not Finding the server at all. The message in Management Studio is that the server was not found, not that a connection was refused due to invalid credentials. I'm not sure that really matters, though, since the code isn't even seeing the servers, which is FAR before any connection can be attempted. After all, on a different network, I could see DBs that I had no access to.

[NeedSomeAnswers]

Let me ask you a question back: I am working under the assumption that the message I would get in Management Studio would be different between Finding the DB, but being unable to connect vs Not Finding the server at all.

I cant really say one way or the other, but i wouldn't bet my house on it!

The message in Management Studio is that the server was not found, not that a connection was refused due to invalid credentials. I'm not sure that really matters, though, since the code isn't even seeing the servers, which is FAR before any connection can be attempted.

So am i reading this correct in that your management Studio on your Win7 box cant see your WIN8 sql server Instance??

hmmm, that would suggest you have a problem with your sql server install or configuration on your win8 box.

I don't know enough about your code to discover sql server instances so i will just talk in term of connection ( as if you get that fixed it should fix both)

When you try to connect to your Win8 SQL server instance through the Win7 sql server management studio what connection details are you using?

[techgnome]

The message in Management Studio is that the server was not found,

That means the SQL Server Express on the target system isn't broadcasting itself as being a valid instance.

Just for squirts and giggles, have you checked what the Startup Type setting is for the SQL Server Express service? It may be set to Manual or Disabled...

-tg

[Shaggy Hiker]

I did look at the Startup Type settings to compare them to the XP system. Much of it means nothing to me, but the two were roughly the same. The start mode is Automatic for SQL Server (three different instances show up), Manual for the Full-text Filter Daemon Lanucher (not even sure what that is), and stopped for the SQL Server Agents, which doesn't bother me as the SQL Server Agents on the XP box are also stopped.

After a bit more examination, I note that the XP box can see the DB on the Win7 system, though it can't connect, which is reasonable, since the Win7 system doesn't have any databases or any logins set up for it, so there isn't anything to connect to. The XP box doesn't see the Win8 DB, either.

Also, I'm not quite sure where I came up with the Win7 running SQL Server 2005, but it is actually 2008 R2, the same as the Win8 box.

[Shaggy Hiker]

So, as I wrote that, I ran the app on Win8 again and saw only two databases (the local and the XP). I then posted that and have run the code on both systems a few more times. The Win8 system is now consistently seeing 4 databases (the local, the XP, and two on the Win7). In other words....and as I wrote that, I got thinking that the Win8 should really be seeing 5, so I went to look and now it is only seeing 2.

In other words, whether or not the Win8 system sees the Win7 system or not is entirely sporadic. I'm not touching the code, the DBs, or anything else, but from one run to the next I may or may not see the Win7 box from the Win8. What is consistent is that I ALWAYS see the XP box, and the Win7 system NEVER sees the Win8 (which is actually the only one that matters). So, it is looking like there is something like a timeout involved with the enumeration, which I believe I have read somewhere. However, it isn't clear why the Win7 system comes and goes while the XP system is always there. If it was just a timeout, then the XP system should be just as unreliable.

In any case, I'm still looking.

[Shaggy Hiker]

Ok, more info and then I'm off to lunch. This one is pretty cool, though not particularly useful.

I have now figured out how to consistently get the Win8 system to see the Win7 DBs both in MS as well as in the code (using the snippet above). To get the Win8 system to see the Win7 DBs, all I have to do is run the code on the Win7 box. It has to execute the snippets noted above, at which point the Win8 system will see the Win7 systems for about a minute. After that window, the Win8 system will no longer see the Win7 system. I haven't timed exactly how long the window is open, though it appears to be at least 30 seconds, and not more than 120 seconds.

What this indicates is that the code shown is causing some change in the Win7 system such that the local DB becomes visible and remains visible for a brief window of time before becoming invisible again. It is acting as if a service is being started, left running for a bit, then shutting down. In any case, the Win7 system never sees the Win8 machine, so this appears to be a one-way street.

[techgnome]

I wonder... could it be a network issue? Almost sounds like the LAN connection is going to sleep or something. o.O weird.

-tg

[Shaggy Hiker]

It appears to be specific to SQL Server. The test I just ran is to start the program on the Win7 system, which would make the Win7 DB visible to the Win8 system for a time. I waited until that time elapsed and started the program on the Win8 system. All that time, the Win7 system had been sitting listening for UDP messages, but otherwise doing nothing. I confirmed that sufficient time had elapsed by checking to see that the Win8 app couldn't see the Win7 SQL Server. However, the Win8 system was sending out UDP pings, and the Win7 system was receiving them. Therefore, broadcast UDP was getting through on the LAN, along with file sharing, but SQL Server was not.

Time to hit Google a bit.

[Shaggy Hiker]

Well...I think it is kind of solved. Unfortunately, I'm not fully certain what solved it. I ended up doing a couple things and undoing a couple things, and somewhere along the line, it got resolved...thus far.

What I believe did it was that I added a rule to the firewall to allow UDP on port 1434, which is the port and protocol used for the SQL Server Browser service. I also set that service up to start automatically. The other thing I did was move all the TCP ports back to dynamic ports away from the static 1433 port. Some combination of those three solved the problem, though I'm not quite sure which one did it. I'm still having an issue with connecting, but that's almost certainly an issue with invalid credentials. They certainly SHOULD be invalid, so that doesn't bother me any.

I'm still not clear as to why running the app on the Win7 system caused the Win8 system to be able to see it for a brief window of time, but that's not relevant to this issue.