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.
:{>
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.
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
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.
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
I updated the Security Testing portion of this post for clarity. Thanks again Alex.
:{>
No problem, Andy. Happy programming and unit testing!
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!!