CREATE SQL Login using Active Directory

  • Ed7

    SSCrazy Eights

    Points: 8352

    Hello SQL Guru,

    I’m newbie to create SQL Login that utilized Active Directory.

    I have a SQL Server 2008 Enterprise edition 64-bits that runs in Window Server 2008 R2 Standard Edition 64-bit operating Systems.

    I configured the SQL Server 2008 with Window Authentication.

    Then, I login with built-in Administrator account into computer and connected to my SQL Server 2008.

    e.g: Object Explorer displayed COMPUTER NAME\Database Instance Name (SQL Server 10.0.2531 – COMPUTER NAME\Admin Account)

    Then, I created SQL login on the following:

    USE AccountingDatabase;

    GO

    CREATE USER DomainName\User Name FOR LOGIN DomainName\User Name

    GO

    EXEC sp_addrolemember N’db_datareader’, N’DomainName\User Name’

    GO

    I tested it out and the specific login still did not have an access to specific database instance e.g: “AccountingDatabase”

    Also, I did not see the “User Mapping” under properties checked/assigned to those specific user.

    Can anyone help and give me light on best practice.

    Thanks,

    Edwin

  • Shawn Melton

    SSC-Insane

    Points: 24675

    You have to CREATE LOGIN first, then CREATE USER. If a LOGIN does not exist for the user in the database it will never be able to login to SQL.

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • Toby White

    SSCertifiable

    Points: 6127

    CREATE LOGIN [DomainName\User Name] FROM WINDOWS with DEFAULT_DATABASE=[AccountingDatabase]

    What Login did you map the user to? Another thing you can try is

    USE AccountingDatabase

    GRANT CONNECT TO [DomainName\User Name]

    Regards,

    Toby

  • Ed7

    SSCrazy Eights

    Points: 8352

    Hi Toby,

    Many thanks for your kind advice and help.

    It is working now after I executed:

    GRANT CONNECT TO [DomainName\User Name]

    Go

    Questions:

    What is the best practice to create SQL Login utilized Active Directory?

    Best regards,

    Edwin

  • Toby White

    SSCertifiable

    Points: 6127

    The most important thing about the "best practice" is that you use Active Directory accounts. In other words DON'T use SQL Server account. From there the next best practice is to grant minimum security needed to get the job done. The next would be to generally grant permissions to Active Directory groups instead of users where possible.

    Either way, you have the right idea.

    Create login

    Grant server level permissions or membership to roles

    Create database user account that maps to login

    and then grant database permissions

    and then grant database role membership

    Regards,

    Toby

  • Gift Peddie

    SSC Guru

    Points: 73570

    The most important thing about the "best practice" is that you use Active Directory accounts. In other words DON'T use SQL Server account.

    The above is only applicable if there is no application context because when there is Asp.net context the above means your application must be Intranet and users number is limited to 1,000 or less.

    Kind regards,
    Gift Peddie

  • Toby White

    SSCertifiable

    Points: 6127

    Use connection pooling from the IIS app pool. IIS will connect to the database with one account. You are not going to create an account for everyone on the internet. Whatever account IIS connects with is the account that you grant permissions to in the database.

  • Gift Peddie

    SSC Guru

    Points: 73570

    Toby White (2/26/2010)


    Use connection pooling from the IIS app pool. IIS will connect to the database with one account. You are not going to create an account for everyone on the internet. Whatever account IIS connects with is the account that you grant permissions to in the database.

    That is actually not relevant because some applications for security reasons every user must connects to the database. What is relevant is AD primary task is to resolve user to local network and file system permissions, that is the reason Asp.net AD providers uses either IIS or LDAP to resolve users before passing users to AD for final resolution.

    Kind regards,
    Gift Peddie

  • Toby White

    SSCertifiable

    Points: 6127

    Then just use active directory groups and grant permissions to the groups on the SQL Instance.

  • Ed7

    SSCrazy Eights

    Points: 8352

    I tried to digest you all discussion and a little heavy for me 🙂

  • Gift Peddie

    SSC Guru

    Points: 73570

    Toby White (2/26/2010)


    Then just use active directory groups and grant permissions to the groups on the SQL Instance.

    When there is an application context both Winform and Asp.net resolves users based on what is allowed by each runtime AD resolution is not included because there is no valid AD ACL(access control list) propagation within application context.

    Winform comes with Client Profiles and Asp.net comes with membership providers.

    Kind regards,
    Gift Peddie

  • Gift Peddie

    SSC Guru

    Points: 73570

    Edwin-376531 (2/26/2010)


    I tried to digest you all discussion and a little heavy for me 🙂

    That is correct just remember not to define users permission with AD only if some of your users require using these permissions with an application.

    Kind regards,
    Gift Peddie

  • d_gomes2

    Old Hand

    Points: 346

    Hi All

    Well in my understandably , I think the ISS example gave here show us the correct scenario.

    Because there is a idea here of 2 situations the Systems Architect design by definition you have one application server and one database server or Storage, so the users are not allow to do operations in the DB except DBA and administrators.

    So why give permissions to AD users if any user have a need that must come from the application server to have that functionality otherwise there is no sense I know Microsoft advise that but sure why not is Microsoft.

    The other situation is to have types o authentication of windows , mixed ou SQL.

    Well this is my opinion I do not think to mapped AD user into SQL server as a user to do operations is a Good Idea.

    Regards

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply