SQL server 2005 remote connection problem

Question

I installed SQL Server 2005 express on two computers. I installed SQL Server Management Studio Express on both computers. I ran each instance of Management Studio and connected to SQL Server using Windows authentication (one computer's connection example: "A-63A9D4D7E7834\SQLEXPRESS"). I created a database named "test1" and created a few tables with data saved.

I did everything this site says: How to configure SQL Server 2005 to allow remote connections", except that I have disabled the firewalls completely.

I connected to A-63A9D4D7E7834, started "SQL Server Management Studio Express" on computer A-63A9D4D7E7834: sever name: "ALL-E425BE6C41D\SQLEXPRESS"
authentication: "windows authentication"
and hit 'Connect'. I get the following error:

Cannot connect to ALL-E425BE6C41D\SQLEXPRESS.

ADDITIONAL INFORMATION:

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

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

What can I do to resolve this?

Answer 1

Unless you have a login on the SQL Server for ALL-E425BE6C41D\Guest your login will fail. If you are using Windows Authentication, then log in to your machine and fire up SQL Server Management Studio Express under the account that has privileges on the SQL Server - usually the account you installed it under.

Answer 2

What if you do the following:

Open SQL Management studio on both PC's and perform the following steps:

• Enable mixed authentication mode (Server properties > Security)

• In Object Explorer create a new user (Security > Login)

  • In new user window fill in the obvious stuff (uncheck passwod expire box!) and go to User Mappings and assign databases to the user and assign 'dbOwner' role.

  • Now, from machine A, connect to a new DB Engine and provide the server name of server B and use SQL server authentication. Can you tell what error you get?

I just have tested this and for me it works all fine.