CODEWITHSUNDEEP
sqlactive-directory
kazinix
kazinix

Reputation: 30123

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 group 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.

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

Thanks in advance!

Upvotes: 3

Views: 3453

Answers (2)

user596075
user596075

Reputation:

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.

Upvotes: 2

billinkc
billinkc

Reputation: 61249

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

Upvotes: 1

Related Questions