question

Tim 2 avatar image
Tim 2 asked

Can't connect to SQLEXPRESS server remotely from SSMS - Login failed

This problem concerns a network connection between what I'll call machine A and machine B. On machine A, I can use SSMS to connect to a local database with no problem. But I'm having trouble getting SSMS on machine B to connect to SQLEXPRESS on machine A. I couldn't even get it to find the instance of SQLEXPRESS on machine A until I created a Guest user account with full permissions for the folder containing the database file. But when I try to connect to the server I get the error message below. I admit that at this point I'm lost. I don't know anything about SQL Server permissions, accounts and all that stuff - I've only been a local database user/VB programmer till now. Can anyone figure out what I need to do, or at least refer me to a good book that will give me some direction? Here's some more info about my configuration;

I'm using SQL Server Express 2008. I have Sequel Server Browser running on machine A. I have also shared the folder containing the database file, though I don't know if I really need to do that. I have TCP/IP enabled under SQL Server Network Configuration from the Configuration Manager.

TITLE: Connect to Server

Cannot connect to PDS\SQLEXPRESS.


ADDITIONAL INFORMATION:

Login failed for user 'PDS\Guest'. (Microsoft SQL Server, Error: 18456)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

sql-server-2008ssmsconnectionsql-server-express
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

CirqueDeSQLeil avatar image
CirqueDeSQLeil answered

Is PDS your computer name or is it your domain?

Have you granted permissions into the database for that account?

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

PDS is the computer name. I don't know how to grant permissions into the database. That's my problem. I don't know anything about that subject.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered

SQL Server Express has some default settings that can potentially cause connection problems from different machines and also how many connections are possible.

If you havent set them since installation then you need to check:
- TCP/IP connections are allowed SQL
- Browser service
- Windows firewall

details here: http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx - HOW TO: CONFIGURE EXPRESS TO ACCEPT REMOTE CONNECTIONS
http://support.microsoft.com/kb/914277 - How to configure SQL Server 2005 to allow remote connections

8 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I did turn off the firewalls in both machines, Norton Internet Security on one machine and Trend Micro Internet Security on the other. I also have the Browser service running. In the configuration manager I enabled TCP/IP in Protocols for SQLEXPRESS under SQL Server Network Configuration, but also in Client Protocols under SQL Native Client 10.0 Configuration. I also have Name Pipes disabled in both configurations, but I don't know what to do with the other items; Shared Memory & VIA
0 Likes 0 ·
Have you tackled windows firewall too? you shouldnt have to disable named pipes, its just an alternative to TCP/IP. Have you changed the maximum concurrent connections value too?
0 Likes 0 ·
The windows firewall is off too. I didn't change the settings for the concurrent connections, so I supposed it's at the default. I forget where to look for that. Could you tell me where? Thanks.
0 Likes 0 ·
right click server in SSMS object explorer | properties | connections tab ... Robert is your Dad's brother ...
0 Likes 0 ·
The maximum number of concurrent connections is set to 0, which should be unlimited. "Rober is your Dad's brother" had me going for a while. I see your from London so I guess that is a Britishism. Perhaps equivalent to "Bob's your Uncle"?
0 Likes 0 ·
Show more comments
softgoods_int avatar image
softgoods_int answered
Ah, "the coin dropped" (another UK-ism, ah how I miss the old country, even though I only lived there a year), but back to business. Yes, I had this problem, and voila, your explanation made it clear, everyone was happy so long as the MDF file was accessible via windows shared drive. I'm still talking that I connected to the instance of SQL Express service running on the "other" PC, but I also can see the MDF file in windows, if I drill down to it, on the shared drive, that's local on the other PC. On the system where this fails, this is not the case, I'm convinced once I share that drive it will also work. Cheers all
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jayant dass avatar image
jayant dass answered
Hi All , You can have a look on below links. https://sites.google.com/site/jayantdass/how-to-find-guest-account-is-enabled-or-disabled-sql-server Thanks & Regards Jayant dass
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jayant dass avatar image
jayant dass answered
Hi All, You can have a look on below links. https://sites.google.com/site/jayantdass/how-to-find-guest-account-is-enabled-or-disabled-sql-server Thanks & Regards Jayant Dass Jayant.dass@gmail.com
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.