Managing security and Certificates

Question

Managing security and Certificates

Dean Jones-454305

SSCarpal Tunnel

Points: 4804
More actions
May 12, 2009 at 10:39 am

#134224

Hi All
I have heard about the various enhancements to security configuration in SQL 2005, I was wondering how other people manage SQL logins in your respective environment, how would you manage users, I assume we all script this, what if a user needs access to update a table, would you simply open your script and change this to reflect the new change ?
Also, I have heard about certificates and was wondering how these are used and in what circumstances would you use certificates ?
I have a situation where an application needs SQL access to a database, this involves giving developers the application login and also the password as well, I was wondering whether are any alternatives to taking this approach ?
Also, how can I further lock SQL down to make sure that its only that application which can use that login, in effect stopping other people from using the login meant for the application.

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

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

Dean Jones (5/12/2009)
I have a situation where an application needs SQL access to a database, this involves giving developers the application login and also the password as well, I was wondering whether are any alternatives to taking this approach ?
Also, how can I further lock SQL down to make sure that its only that application which can use that login, in effect stopping other people from using the login meant for the application.

I manage this by having different passwords in Production and Development for the application logins. Developers can develop using their own logins to the SQL Server for the most part. When it comes to testing, the app login is generally good to use so that the permissions are correctly scripted when migrating to production.

If you want to block a login from accessing SQL Server except from a specific application name, you can look at the following blog post I made last year:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/06/18/securing-production-servers-from-developers-with-ssms-and-application-usernames-passwords.aspx