Managing security and Certificates

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Managing security and Certificates

Managing security and Certificates

Rate Topic

Display Mode

Topic Options

Author

Message

Dean Jones-454305

Posted Tuesday, May 12, 2009 10:39 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, February 15, 2013 2:29 AM
Points: 282, Visits: 1,047

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.

Post #715288

Jonathan Kehayias

Posted Tuesday, May 12, 2009 11:31 AM

SSCommitted

Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696, Visits: 1,742

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

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs

Post #715320

« Prev Topic | Next Topic »

Permissions