question

Jeremy H 1 avatar image
Jeremy H 1 asked

Connect an application to a SQL Server database using Windows Authentication and Application Role

I have a VB (VS2008) application which uses a standard db connection string to log into the SQL Server (2005 / 2008) database that it uses, with Windows Authentication mode:

Data Source=HostName\DBMS Instance; Initial Catalog=DatabaseName; Integrated Security=True

This works fine in development, but in the live environment it seems to require each specific user to be set up as a member of a security group within SQL Server in order to gain access to the database and perform the standard db table actions. This is obviously unwieldy as it may require several hundred users to be added, and then extensive maintence to keep the list up to date.

I am assuming that there is a better way of doing this, either by setting up a group with the appropriate permissions to which all members of the organisation belong ("Everyone"), or by creating an Application Role for the target database.

Can I get by with the first option (in which case how), or do I need the second approach, in which case what alteration do I need to make to my code to jump this new security hurdle? (Unfortunately, the SQL DBA doesn't seem to know very much and can't help.)

sql-server-2008sql-server-2005securityroles
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered

Add the users to a domain group (using Active Directory), and then map that to a SQL login.

Assign your database permission to that SQL login.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
+1 - AD groups are far and away my preference.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.