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.)

more ▼

asked Mar 24, 2010 at 06:08 AM in Default

avatar image

Jeremy H 1
21 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Mar 24, 2010 at 07:23 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

  • AD groups are far and away my preference.

Mar 24, 2010 at 07:47 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 24, 2010 at 06:08 AM

Seen: 2394 times

Last Updated: Mar 25, 2010 at 08:14 PM

Copyright 2018 Redgate Software. Privacy Policy