Active Directory User/Groups + SQL Server - Client Cannot locate SQL Server?

I'm doing research on how to integrate users or groups in Active Directory to SQL Server so that users can simply login to Windows and use the application (the connection is thru Windows Authentication).

What I did so far:

1. Install Windows Server 2008 R2 on a machine with the name "TheServerMachine"
2. Set "TheServerMachine" as Active Directory domain "MyDomain" controller
   • Add user User1 to active directory.
   • Add group Group1 to active directory. (Global, Security)
   • Add User1 to Group1
3. Install SQL Server 2008 Express, "SQLEXPRESS" as instance, on "TheServerMachine"
   • Set server to allow remote connection
   • The SQL server is in firewal exception
   • TCP/IP protocol is enabled for the SQL service
   • Setup the database "MyDatabase"
   • Add role "MyRules"
   • Add "MyDomain/Group1" to Login (the dialog box recognizes the 开发者_如何学JAVAgroup and the domain) with the default database "MyDatabase"
   • Add user "Group1Users" to "MyDatabase", added a role "MyRules" and set the login I have created.
4. Add a client machine with the name "Client1" to "MyDomain".
5. Done!

My problem is, I can't access the SQL server in my client PC which is successfully logged in as "User1" in windows. Using these server path in my ConnectionString, it says that server may not allow remote connection, or the server path may be invalid.

• "\\TheServerMachine\SQLEXPRESS"
• "\\MyDomain\SQLEXPRESS"
• "\\192.168.0.1\SQLEXPRESS"
• "TheServerMachine\SQLEXPRESS"
• "MyDomain\SQLEXPRESS"
• "192.168.0.1\SQLEXPRESS"

Note: My connection string is set "Integrated Security=True".

• Do you think it has something to do with my path?
• Is it because the domain and SQL server is on the same machine? (I don't think so)
• Did I miss some configuration to Domain? Active Directory? SQL Server?
• Should I use "SSPI" instead of "True" in "Integrated Security"?

Thanks in advance!

---

If your TCP/IP protocol is enabled, also make sure that SQL Server Browser service is enabled and started (and set it to automatic if you will be receiving connections from clients).

The SQL Server Browser is use for dynamic port allocation for named instances (in this case SQLEXPRESS is a named instance) and reaches out over port 1434. Also ensure that port 1433 and port 1434 are open.

Edit: Ensure that you are working with SQL Server services through the SQL Server Configuration Manager (SSCM), as the Windows Service Manager/Console snap-in doesn't have the added functionality that SQL Server services require.

---

First thing that jumps to mind is that SQLExpress isn't configured for remote connections out of the box. Instructions on how to do that

You might run into some other issues after this but try the things in that article first.

And indeed you did already establish that you configured SQLExpress for remote connections. Next item that popped out was your uncertainty on connection string with Integrated security true vs SSPI. What does the current connection string look like? I would assume something like Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|aspnetdb.mdf

This SO question has plenty of troubleshooting and a wonderful title SQL Express connection string hell ASP.Net