[RESOLVED] SQL 2008 - Can't Login To SQL Remotely
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:
http://i.imgur.com/OyDrO.png
I made sure the services were running,
http://i.imgur.com/kN9d2.png
TCP/IP was enabled,
http://i.imgur.com/h9Rw9.png
And enabled SQL Server login.
http://i.imgur.com/glA3m.png
Pretty sure that's all I did for this.
Any ideas on what I'm doing wrong?
Re: SQL 2008 - Can't Login To SQL Remotely
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.
Re: SQL 2008 - Can't Login To SQL Remotely
Quote:
Originally Posted by
szlamany
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.
Re: SQL 2008 - Can't Login To SQL Remotely
They renamed it in 2008 - sorry...
http://www.mssqltips.com/sqlserverti...l-server-2008/
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...
Re: SQL 2008 - Can't Login To SQL Remotely
Quote:
Originally Posted by
szlamany
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.
1 Attachment(s)
Re: SQL 2008 - Can't Login To SQL Remotely
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!
Re: SQL 2008 - Can't Login To SQL Remotely
I got this link googling for
sql 2008 sql server configured allow remote connections
http://blogs.msdn.com/b/walzenbach/a...rver-2008.aspx
Re: SQL 2008 - Can't Login To SQL Remotely
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.
-tg
Re: SQL 2008 - Can't Login To SQL Remotely
This section s/b renamed Database Development (&.u.prbly.r.server.centic!)
I was still surprised that 2008 lost surface area config app - at least I knew where to find that!
Re: SQL 2008 - Can't Login To SQL Remotely
Quote:
Originally Posted by
szlamany
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.
Re: SQL 2008 - Can't Login To SQL 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?
1 Attachment(s)
Re: SQL 2008 - Can't Login To SQL Remotely
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.
Re: SQL 2008 - Can't Login To SQL Remotely
Quote:
Originally Posted by
szlamany
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.
Thanks for the help, everyone.
Re: SQL 2008 - Can't Login To SQL Remotely
Quote:
Originally Posted by
smendoza
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.
Thanks
Re: [RESOLVED] SQL 2008 - Can't Login To SQL Remotely
Oh and have you tried connecting by PCname/SqlInstanceName ?
Re: SQL 2008 - Can't Login To SQL Remotely
Quote:
Originally Posted by
weirddemon
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 :ehh: I see the prob.
Re: SQL 2008 - Can't Login To SQL Remotely
Quote:
Originally Posted by
smendoza
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 :ehh: 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.
Re: SQL 2008 - Can't Login To SQL Remotely
Quote:
Originally Posted by
smendoza
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!!
