June 23, 2009 at 9:13 am
I'm in charge of configuring, setup, security, etc on all servers as well as database servers. I want the developers to use 1 Id and password per database per application to connect to the application database at a SQL level, the manager wants to use the service account which is used for everything to connect to the databases. I'm getting pushback due to according to him it would be too much overhead to maintain, I suggested for audit purposes, security purposes, etc it would be easier if each app had its own ID and pwd to connect to that application database.
I'm curious on how everyone else is setup.
Do you applications have their own id and password to connect to the database?
Do you have 1 id and 1 password to connect to all databases on the server?
My thought is that a sevice account should not be used to connect to any database unless the app id is locked or deleted, but they use the service account for everything from, app pools, logging into server, running apps, connecting to databases, etc.
June 23, 2009 at 9:53 am
Mike (6/23/2009)
I'm in charge of configuring, setup, security, etc on all servers as well as database servers. I want the developers to use 1 Id and password per database per application to connect to the application database at a SQL level, the manager wants to use the service account which is used for everything to connect to the databases. I'm getting pushback due to according to him it would be too much overhead to maintain, I suggested for audit purposes, security purposes, etc it would be easier if each app had its own ID and pwd to connect to that application database.I'm curious on how everyone else is setup.
Do you applications have their own id and password to connect to the database?
Do you have 1 id and 1 password to connect to all databases on the server?
My thought is that a sevice account should not be used to connect to any database unless the app id is locked or deleted, but they use the service account for everything from, app pools, logging into server, running apps, connecting to databases, etc.
You can put all users in one AD group at domain level and grant access to the AD Group. Users will be able to connect using their NT login. If you have one service account used by everyone, you cannot put accountability on one person.
June 23, 2009 at 9:59 am
I have done that when I have groups hitting a db, but for applications such as web apps, I want to have 1 id and 1 password associated to that web app, so I don't have 20 apps connecting with the same id and pwd
June 23, 2009 at 10:11 am
I'd go with what pradeep has suggested.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 24, 2009 at 3:27 pm
There is no second thought in using NT authentication, but we have to be flexible some times and use a sql authentication as it is not possible always ( particulary if we missed it in the design and implementation phase of an application ). Since it is a web application it's hard to find out the end user's and to collect their NT ID's.
My solution is, don't allow any queries in the application convert all queries and existing stored procedure to a schema based stored procedure. Create a SQL ID and provide only execute permission for that schema.
Using seperate credentials for each application is the better way.
Note : If possible you have to use Pradeep's Solution only.
---------------------------------------------------
Thanks,
Satheesh.
June 25, 2009 at 1:07 am
We use one sql account for each application as all are web based. That account is then ideally given only execute permission on stored procedures and functions.
On older badly written systems it has to have select on tables and views too and on the really awful systems dbo but I'm hoping to scrap or rewrite those soon!
It does mean everyone using that application connects with the same id and password but does enable connection pooling.
Each web application then uses forms based authentication (a user table) to control access to the front end app.
On the intranet we take advantage of the Request.ServerVariables.Item("LOGON_USER") to get the user login and validate against the user table so no passwords are stored or requested.
June 25, 2009 at 5:07 am
So do you have one Id for every databases that your web apps use or do you have 1 ID per database for that web app to use?
example;
database1 = 1 ID + used by multiple web apps
or
database1 = 1 id
database 2 = 1 id
for each web ap that connects to that database?
We have 1 ID that is used by everything, web apps, web services, log into the serverm log into the database server and this 1 ID has full DBO/admin rights to the database as well as the servers.
I would like to use the following setup:
webAppDatabase 1 = 1 ID used by that web app
webAppDatabase 2 = different ID and pwd and used by the web app
webAppDatabase 3 = different ID then db1 and db2 used by the web app
and so on. I would rather not have 1 id used by everything
June 25, 2009 at 5:04 pm
we use 1 AD Service account to connect to the server through a web app. The users are put into a group and that group is given whatever permissions we want. if the user is not in the correct group to write data their AD account will not allow them access to the data.
Basically 1 AD Service account to talk between servers for connection purposes
AD group is created for readonly access, AD group created for Write access
user us put into correct group based on need
Users AD account is autenticating to data through the groups. This way I can tell exactly who is accessing what.
June 26, 2009 at 7:24 am
An account used by an application to connect to SQL Server should never have permission to more than the minimum that it needs. If it has permissions to data from other databases and applications, that is a security risk and should not be allowed. It sounds like the account they are using has way to many permissions for an application account, likely including DDL and configuration type stuff, so no way should they be allowed to continue that. Generally we do what Pradeep posted, but when we have to use a SQL account, it is one created specifically for that application which only has access to the data the application needs.
June 26, 2009 at 8:28 am
I'm confused,if you have the service account connecting to SQL Server, how do you get the individual accounts to come through? Typically we set up for Windows auth, and IIS the same, and let the user's credentials pass through.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 26, 2009 at 8:33 am
Steve Jones - Editor (6/26/2009)
I'm confused,if you have the service account connecting to SQL Server, how do you get the individual accounts to come through? Typically we set up for Windows auth, and IIS the same, and let the user's credentials pass through.
We don't, everything is connecting via the service account, so if anything fails it fails under the service account. Even trying to auth the user (if there is an login screen), it connects to the db via the service account not an ID and pwd for that web app.
We're not using AD for anything, though I'd much rather do that, but our apps don't require authentincation at that level.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply