THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | Join | Help

Home

Blogs

Downloads

Opml

Andy Leonard

Andy Leonard is CSO of Linchpin People and SQLPeople, an SSIS Trainer, Consultant, and developer; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server MVP Deep Dives. His background includes web application architecture and development, VB, and ASP. Andy loves the SQL Server Community!

Database Security Testing

Introduction

Tony Davis (Blog) wrote an interesting post (SA no more! - ha!) about users, developers, and especially third-party applications requiring sysadmin or sa login access. Tony makes an excellent point: "Ultimately, the business needs the application to work. The quickest solution to get the application into production is often to grant sysadmin privileges to the application login, in the production environment, and hope that the auditors don't notice."

Security Testing

One solution is security testing. There are a couple ways to conduct security testing, but one relatively painless way is to create a SQL Login with the Development environment permissions restricted to mimic Production - if you're running Mixed Mode security. Developers can change the connection string to use the SQL Login, and then conduct regression / unit tests.

If you're not running Mixed Mode security, you can create an Active Directory login and restrict the permissions on this domain account. Developers can then login using the test domain account and conduct regression / unit testing.

Conclusion

Security testing is essential and should be done as early in the development process as possible.

:{>

Published Tuesday, November 02, 2010 8:00 AM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

	RichB said: Just remember that sql logins are passed across the network with passwords in plain text. If you haven't ever done it, try running wireshark against a sql login... see if you can guess the password. November 2, 2010 9:56 AM
	andyleonard said: Excellent point Rich, Hopefully the Development environments are burried behind firewalls inside the enterprise, but I did not specify that in my post. Thanks for pointing that out! :{> Andy November 2, 2010 10:17 AM
	Alexander Kuznetsov said: Hey Andy, Suppose I am using only integrated security on my test server. Suppose I have a user testUser with password myPwd in AD. Can you show me how to change the connection string so that the connection uses testUser? Thanks. November 2, 2010 11:05 AM
	andyleonard said: Hi Alex, That part of my post is worded poorly. In fact, I injected the line about using AD after I'd written everything else, because I didn't originally consider instances not using Mixed Mode. i sincerley appreciate you pointing this out. I need to do a better job proof-reading in the future. :{> Andy November 2, 2010 11:13 AM
	andyleonard said: I updated the Security Testing portion of this post for clarity. Thanks again Alex. :{> November 2, 2010 11:20 AM
	Alexander Kuznetsov said: No problem, Andy. Happy programming and unit testing! November 2, 2010 5:31 PM
	granadaCoder said: I think the principle is "Use a limited account to conduct testing". My personal experience is that a sql-authentication-login in DEVELOPMENT will flush out the issues early. Obviously (as suggested) the plain-text password is an issue. For me, plain text passwords in DEVELOPMENT is not an issue. However, I cannot use sql-authentication in production. LocalSandBox=sql-authentication Developer Environment (databases not on my local machine but still before QA gets to them)=sql-authentication QA environment=sql-authentication Staging=Windows Authentication Production=Windows Authentication If you are not running mixed mode, then (as already suggested) creating a windows-account and giving it permissions is the way to go. My preference is to "code up" permissions. GRANT EXECUTE ON [MySchema].[uspEntityGetAll] TO someSqlAuthenticationUser , [MyDomain\myGroup] , [MyDomain\_mySpecificUser] GO Doing this up front (with a limited account) will prevent deployment issues in the area of "EXECUTE permissions denied (blah blah blah) on such such object". Which is the tell-tale sign of bad deployment planning. Of course this is (yet) another place where sqlcmd (and running in sqlcmd mode) is a clear winner. (And of course I learned this type of approach from Andy during one of his presentations). You code to a $(Variable) instead of a hard-coded value. /* MYLIMITEDACCOUNT could be set to any one of the following: someSqlAuthenticationUser , [MyDomain\myGroup] , [MyDomain\_mySpecificUser] */ :setvar MYLIMITEDACCOUNT "someSqlAuthenticationUser" GRANT EXECUTE ON [MySchema].[uspEntityGetAll] TO $(MYLIMITEDACCOUNT) GO If you're looking for "sqlcmd mode", it on SSMS menu near "Query / Results To" (at least in SSMS 2008 that's one place it is). Hopefully, people are thinking ~about deployment (way) ~before the deployment actually occurs. //Conclusion Security testing is essential and should be done as early in the development process as possible.// True DAT!! November 12, 2010 4:18 PM