I setup SQL Server 2008 Express on a new server. This is a low volume server and we don't need all the bells and whistles the full version of SQL gives us. So the Express version should work fine.
I've setup SQL Server Express on a server before, so I'm a little stumped at what's going on.
Using the sa account, I can log in to SQL from the server just fine. But when I try to log in remotely, I get the following error:
There is a Surface Area config program that you need to find - START / PROGRAMS under SQL...
Tell it you want to allow REMOTE CONNECTIONS - they are turned off by default in EXPRESS.
Can you be a bit more specific? I didn't find any application named as such and when I Google Surface Area Configuration, all I get is the Facet Viewer. I enabled the options in there that seemed applicable, but the same error occurred.
Seems you might be able to do it from SSMS from the server under FACETS as well - at least that link seems to say so...
I've never had to use this tool before. So I'm skeptical about whether or not it will work. Do you know which property needs to be enabled? I'm experimenting with different ones, but nothing is working so far.
Well - I was surprised to see they re-worked it for SQL 2008 - and I've never had to setup 2008 express so I can't give you more details.
...and I have been using this tool for the past 10 years - it's real!
Here is what the old Surface Area Config tool screen looked like - very clearly telling you that EXPRESS doesn't allow REMOTE connections by default and a very clear "LOCAL AND REMOTE" radio button.
I don't know what this new-fangled facet thing is all about - sorry!
*** Read the sticky in the DB forum about how to get your question answered quickly!! ***
Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".
Ahh... one note, I think szlamany kind of assumed it... this should be done from the SERVER you're trying to connect to... you CANNOT do it from your local machine... you'll need to login into the server and configure it from there.
I wish this was as easy as just finding a random Google page. I saw this article a week ago. I followed the steps, but none of it worked. I turned my firewall off for now, so it won't interfere. I disabled my antivirus and added a port for SQL.
The other steps were also done a long time ago as well. I already enabled remote connections and TCP/IP.
I assure you, if it was a quick and simple solution, I would have found it by now.
That message is so standard though - gotten it a lot and always found it easy to overcome - let's see...
Firewall you say is off - port 1433 for tcpip and port 1434 for udp a required.
There is some way to find out what on your machine is listening one what port - some cmd prompt thing. Can you run that on the server and see if those ports are associated with the service?
*** Read the sticky in the DB forum about how to get your question answered quickly!! ***
Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".
Well you should probably tell us more about your setup since you said you've tried some things that have been suggested. Also since we don't know what you have tried we telling you to check the most common problems first.
I know one thing that caused me a problem was inside of the tcp/ip Properties even though it shows enable it wasnt configured. Double click on the tcp/ip and for me I enable port 1433 on all the adapters. After doing that I didn't have any problems connecting remotely.
That message is so standard though - gotten it a lot and always found it easy to overcome - let's see...
Firewall you say is off - port 1433 for tcpip and port 1434 for udp a required.
There is some way to find out what on your machine is listening one what port - some cmd prompt thing. Can you run that on the server and see if those ports are associated with the service?
I got to playing around more with the TCP/IP and figured it out. It's one of those dumb things.
When I read that article and it said to set the port to 1433, I didn't look closely enough at the images. I only set it within the client protocols. But when I looked in the IP Address tab for Protocols For SQLEXPRESS, I saw that the port was empty on all of the adapters, except one.
So I changed them all to 1433 and it appears to be working. I'm at least able to log in remotely now.
Well you should probably tell us more about your setup since you said you've tried some things that have been suggested. Also since we don't know what you have tried we telling you to check the most common problems first.
I know one thing that caused me a problem was inside of the tcp/ip Properties even though it shows enable it wasnt configured. Double click on the tcp/ip and for me I enable port 1433 on all the adapters. After doing that I didn't have any problems connecting remotely.
Yeah. That's what it was. I figured it out right before you posted and was making my reply on solving the issue just as you replied.
Yeah. That's what it was. I figured it out right before you posted and was making my reply on solving the issue just as you replied.
Thanks
Ah yes, I've felt your pain with that one before. I pulled my hair out for 2 hours then I walked away and checked all the setting again and was like ohhh I see the prob.
Ah yes, I've felt your pain with that one before. I pulled my hair out for 2 hours then I walked away and checked all the setting again and was like ohhh I see the prob.
It's crazy because I've setup SQL a couple of SQL servers before, but I've never had to set this property. It's always been done automatically.
Well you should probably tell us more about your setup since you said you've tried some things that have been suggested. Also since we don't know what you have tried we telling you to check the most common problems first.
I know one thing that caused me a problem was inside of the tcp/ip Properties even though it shows enable it wasnt configured. Double click on the tcp/ip and for me I enable port 1433 on all the adapters. After doing that I didn't have any problems connecting remotely.
This is great information - this was exactly where I needed to put in a port 1433 value to get remote connections to work.
Thanks!!
*** Read the sticky in the DB forum about how to get your question answered quickly!! ***
Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".
[RESOLVED] SQL 2008 - Can't Login To SQL Remotely
Originally Posted by SJWhiteley
Reply With Quote
I see the prob.
