none
ConnectionString to SQL Server RRS feed

  • Question

  • In .NET, is it possible to pass a Windows Account username / password to SQL Server? like this

    String connectionString = @"Server=ServerName123;Database=DBName123;User Id=DomainName\AccountName;Password=xxxxxx;";

    The goal is to use Active Directory as the source of truth for authentication, not SQL Server.

    We use PasswordVault AIM (Application Identity Management) agent installed on web server to fetch password programatically at runtime. Hence, the connectionString will be built on fly.

    Benefit of PasswordVault is that it can initiate password rotation automatically against AD. AIM agent can then obtain passsword on demand.

    The problem we are facing seem related to the fact that AD account is not allowed in the connectionString to SQL Server. And it only accept SQL Login, which defeats the purpose of having a single source of truth.

    Anyone can confirm if my assumption here is correct?

    Thanks,

    David.


    Thursday, July 3, 2014 7:19 PM

Answers

  • You can setup a SQL database to use any local windows user account.  You can have two situations

    1) The remote PC and the PC where the SQL database are in the same Group.  Then individual credentials aren't needed.  You can setup one windows user group account and add all users to the group.  Then link that user account to the credentials in the SQL database.  You can then use Trusted_Connection=True;

    2) The remote PC and the SQL database PC are not in the same group.  The access depends on the version of windows.  To get access to the database you will need to get through both the windows credentials and the SQL credentials.  The SQL database is a http service using port 80 or 8080.  A remote PC will need to be able to connect to the service and get through any security settings.  You can add any windows user account to the access list for the SQL server.  The credentials in the connection string will be verified through the windows user account.


    jdweng

    Thursday, July 3, 2014 9:56 PM
  • Being in the same domain may not mean being in the same group.  Large companies can have sites all across the country with the same domain, but not all sites will have full access to every computer.  Different divisions of the company may have there own groups.

    You can setup one group account in windows and add as many users to the group as you want.  Then only have one SQL account where you can select windows authentication using the one group account.  then you don't have to add individuals to the SQL account.  When you have Group Policies enabled where all computers use the same password server you just have to add the users to the group either at the password server or in the SQL Server windows user account.

    We usually have two SQL Server database user  accounts.  One for admin's and one for normal users.  Both are attached to the same windows group account, but the admin's have more access in the SQL database than normal users.


    jdweng

    Tuesday, July 8, 2014 9:53 PM

All replies

  • You can setup a SQL database to use any local windows user account.  You can have two situations

    1) The remote PC and the PC where the SQL database are in the same Group.  Then individual credentials aren't needed.  You can setup one windows user group account and add all users to the group.  Then link that user account to the credentials in the SQL database.  You can then use Trusted_Connection=True;

    2) The remote PC and the SQL database PC are not in the same group.  The access depends on the version of windows.  To get access to the database you will need to get through both the windows credentials and the SQL credentials.  The SQL database is a http service using port 80 or 8080.  A remote PC will need to be able to connect to the service and get through any security settings.  You can add any windows user account to the access list for the SQL server.  The credentials in the connection string will be verified through the windows user account.


    jdweng

    Thursday, July 3, 2014 9:56 PM
  • Are all the AD users valid SQL Server Logins at this point?  Is your SQL Server authentications set to allow Windows Authentication mode?


    Brent Spaulding | Access MVP

    Thursday, July 3, 2014 10:24 PM
  • All AD Users are valid SQL Login.  Yes - SQL configured to allow Window Authentication mode.

    Both the web server and SQL physical server are in the same domain. Does that qualify them as in the same group?  

    I am intrigued by option 1.  We have multiple dedicated svc accounts for application to connect to SQL.  One per application.  Do you mean to add these svc accounts into one single AD group, and grant the group permission to access/execute stored proc, etc...? 

    Thanks,
    David.
    Tuesday, July 8, 2014 8:02 PM
  • Being in the same domain may not mean being in the same group.  Large companies can have sites all across the country with the same domain, but not all sites will have full access to every computer.  Different divisions of the company may have there own groups.

    You can setup one group account in windows and add as many users to the group as you want.  Then only have one SQL account where you can select windows authentication using the one group account.  then you don't have to add individuals to the SQL account.  When you have Group Policies enabled where all computers use the same password server you just have to add the users to the group either at the password server or in the SQL Server windows user account.

    We usually have two SQL Server database user  accounts.  One for admin's and one for normal users.  Both are attached to the same windows group account, but the admin's have more access in the SQL database than normal users.


    jdweng

    Tuesday, July 8, 2014 9:53 PM