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

Question

question

Tim 2 asked Apr 6, '10

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-2008 ssms connection sql-server-express

10 |1200

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

Answer 1 · 2010-04-06T18:50:32Z

CirqueDeSQLeil answered Apr 6, '10

Is PDS your computer name or is it your domain?

Have you granted permissions into the database for that account?

1

10 |1200

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

Tim 2 commented · Apr 07, 2010 at 10:51 AM

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 ·

Answer 2 · 2010-04-07T05:35:15Z

Fatherjack answered Apr 7, '10

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

10 |1200

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

Tim 2 commented · Apr 07, 2010 at 11:04 AM

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 ·

Fatherjack ♦♦ commented · Apr 07, 2010 at 11:11 AM

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 ·

Tim 2 commented · Apr 07, 2010 at 02:52 PM

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 ·

Fatherjack ♦♦ commented · Apr 07, 2010 at 04:14 PM

right click server in SSMS object explorer | properties | connections tab ... Robert is your Dad's brother ...

0 ·

Tim 2 commented · Apr 07, 2010 at 05:12 PM

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 ·

Show more comments

Answer 3 · 2010-09-15T10:51:04Z

softgoods_int answered Sep 15, '10

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

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

Answer 4 · 2011-08-12T00:20:16Z

jayant dass answered Aug 12, '11

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

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

Answer 5 · 2011-08-12T00:21:47Z

jayant dass answered Aug 12, '11

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

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

question