CREATE SQL Login using Active Directory

Question

CREATE SQL Login using Active Directory

Ed7

SSCrazy Eights

Points: 8519
More actions
February 26, 2010 at 10:11 am

#230319

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

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

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

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 1

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: 6187 More actions · Answer 2

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: 8519 More actions · Answer 3

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: 6187 More actions · Answer 4

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 More actions · Answer 5

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: 6187 More actions · Answer 6

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 More actions · Answer 7

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: 6187 More actions · Answer 8

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

Ed7 SSCrazy Eights Points: 8519 More actions · Answer 9

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

Gift Peddie SSC Guru Points: 73570 More actions · Answer 10

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 More actions · Answer 11

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 SSC-Addicted Points: 406 More actions · Answer 12

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