locked
sql server using active directory RRS feed

  • Question

  • In a sql server 2008 database, the person that has dba access thinks the user security rights are controlled by the active directory. The .net application and database was written by contractors that are no longer with the company. There is no documentation for the application.

    Thus we are trying to determine how the security is setup. I stepped through the .net 2012 code and found what active directory values were setup. However with sql server 2008, I do not know how to tell how the security is setup via active directory contols.

    Thus can you tell me and/or point me to a reference that will tell me how to accomplish my goal?

    Sunday, February 9, 2014 4:30 AM

Answers

  • hope below link will be useful..

    http://stackoverflow.com/questions/5029014/how-to-add-active-directory-user-group-as-login-in-sql-server


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    • Proposed as answer by Kalman Toth Sunday, February 9, 2014 8:20 AM
    • Marked as answer by wendy elizabeth Monday, February 10, 2014 3:29 AM
    Sunday, February 9, 2014 6:57 AM
  • The connection string determines the authentication method used to connect to SQL Server.  When Integrated Security=SSPI or Integrated Security=true are specified, Windows authentication is used and the Windows account the application is running under determines the security context.

    A client WinForm app will use the security account of the interactive user when Windows authentication is specified.  A Windows service application will use the service account specified in service control manager.  An IIS application will use the Windows account specified as the .NET application pool security context.  However, it is possible to use a different account via configuration or code.

    To reduce security administration, a common practice is to use Windows authentication and provide access via Windows Group membership instead of adding each Windows user account individually.  Only the AD group is added as a SQL login and database user.  Permissions are then granted to the AD group or database role the account is a member of.

    If you see references to AD permissions in the code, this may be permissions to application features controlled via the application code rather than direct database permissioning.  This could be done using the APS.NET membership provider (http://msdn.microsoft.com/en-us/library/tw292whz(v=vs.100).aspx) or custom code.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, February 9, 2014 1:17 PM

All replies

  • Does that mean  the application connects to SQL Server via Windows Authentication?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, February 9, 2014 6:39 AM
    Answerer
  • hope below link will be useful..

    http://stackoverflow.com/questions/5029014/how-to-add-active-directory-user-group-as-login-in-sql-server


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    • Proposed as answer by Kalman Toth Sunday, February 9, 2014 8:20 AM
    • Marked as answer by wendy elizabeth Monday, February 10, 2014 3:29 AM
    Sunday, February 9, 2014 6:57 AM
  • The connection string determines the authentication method used to connect to SQL Server.  When Integrated Security=SSPI or Integrated Security=true are specified, Windows authentication is used and the Windows account the application is running under determines the security context.

    A client WinForm app will use the security account of the interactive user when Windows authentication is specified.  A Windows service application will use the service account specified in service control manager.  An IIS application will use the Windows account specified as the .NET application pool security context.  However, it is possible to use a different account via configuration or code.

    To reduce security administration, a common practice is to use Windows authentication and provide access via Windows Group membership instead of adding each Windows user account individually.  Only the AD group is added as a SQL login and database user.  Permissions are then granted to the AD group or database role the account is a member of.

    If you see references to AD permissions in the code, this may be permissions to application features controlled via the application code rather than direct database permissioning.  This could be done using the APS.NET membership provider (http://msdn.microsoft.com/en-us/library/tw292whz(v=vs.100).aspx) or custom code.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, February 9, 2014 1:17 PM