SQL Server 2005 Express over LAN – Login Failed for ‘Servername\Guest’

sql-server-2005

Posted my question here because I'm pretty sure it's less of a program problem and more of a Server problem.

I'm developing a C# program that will access a database on a server. If I run the program on my PC (the Server, Server-PC), it works fine.

Copying the programs to two other computers (tried it just one at a time, though) connected to my PC via network, and suddenly the program just fails to connect.

On a XP-based PC, the error message is
"Login failed for user Server-PC\Guest"

Wwhile on a Vista based PC, the error message is
"Login failed for user 'null'. The user is not associated with a trusted SQL Server connection."

This is my connection code:

\SQLEXPRESS;Database=GGDBase;Integrated Security=SSPI;Trusted_Connection=true;Persist Security Info=False;

Now, I don't know much about SQL Server setup, adding user permissions and the such, but I have done the following:

  • Enabled the TCP/IP Protocol in the Server Configuration Manager
  • Set the server to "SQL Server and Windows Authentication Mode"
  • Allowed the program through the firewall.

Could I please have some advice on this? It simply won't work. What else could I do? Thanks to all who answered.

P.S. In case it is important:

  1. My Server is running Windows 7.
  2. The other two computers I used to try and connect are running
    Windows XP (SP3) and Vista. They are all connected to a network.
    Yes, they can share files with each other.
  3. I'm using the EXPRESS edition of SQL Server 2005.
  4. The program I am developing is a desktop application created in C#,
    so I doubt those "do something in IIS" solutions will apply.

Best Answer

I would assume that these computers are not members of a Windows domain. You are trying to connect to the computers using Windows authentication which won't work as there is no common password repository such as a Windows Active Directory domain.

To fix this you need to create a login within SQL Server then give it rights within the database. To do this connect to the SQL Server with Management Studio on the server's console. Navigate to Security > Logins. Right click on Logins and select "New Login" from the drop down. In the new window select "SQL Server Authentication" put in a username and password. Uncheck all the check boxes. Go to the User Mapping page (white pain on the left) and give the login the rights that it needs in the database. Then change your connection string in the app to what I show below replacing the myUsername and myPassword with the username and password you specified when you created the login.

\SQLEXPRESS;Database=GGDBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False;
Related Topic