June 30, 2011 at 10:41 am
Hi,
I have an asp.net site with sql 2008 R2 backend. The site is still in the development stage, but I want to implement a least privilege account sooner rather than later. The site uses forms authentication.
I have just been using the sa account for everything so far. How do I go about setting up a least priv account? Do I add a new Login from the security menu in the management studio? If so, what Server Roles, User Mappings/Database role memberships should be set?
Also, should I consider encrypting columns or tables with sensitive information?
How do I go about encrypting tables, columns, or the entire DB?
I'm pretty new to all this, any security advice would be much appreciated.
Thanks in advance.
June 30, 2011 at 11:09 am
Wow, that is quite a set of questions.
And an entire design job.
First using sa at ALL is a bad idea. I recommend switching to using windows logins for your primary access. Also the sa password should be long and strong.
Many of your questions are covered in books online.
Forms authentication doesn't really define SQL access, how are usernames/passwords authenticated? AD? SQL?
To add a login you do it from the Security, then logins area. When you add a login you can chose SQL or windows. From your app it will probably be SQL. Use a long and strong password. Don't give it ANY server rights or any database level rights. DO select your database which gives it the ability to get into that database.
You should be granting rights to roles and not specific logins.
Your code should grant EXEC rights to sprocs to those roles.
Direct access to tables or views from the front-end should not be allowed, views *maybe* tables NO.
Define sensitive.. Many people over-define it. I rarely recommend encrypting an entire database, specific columns usually are more than sufficient, but you need to think about your goals with this level of protection and who you are protecting the data from.
CEWII
June 30, 2011 at 12:41 pm
Hi Elliott,
user/pass is authenticated using SQL.
I added a new login and it uses SQL Authentication. Set the default database to the db that it needs access to.
In the Server Roles page, public is the only item selected.
In the User Mapping page the DB is selected with the new login as the user and default schema is set to dbo. Public is the only item selected in the Database role membership section.
Nothing is configured in the Securables page.
I updated my web.config to reflect the changes but was throwing an error saying "Invalid value for key 'integrated security'." I changed integrated security to false, then SSPI, but was still throwing the same error. I then removed the integrated security tag and is now throwing a "Login failed for user 'MyNewAccount'.
What don't I have configured correctly?
June 30, 2011 at 12:59 pm
So basically we are to a login failed?
That is usually a bad password. No here is the big question, how is the server configured for login auditing, you can find out by right clicking on the server name in SSMS and selecting properties. The security page list Login auditing, but default it should have "Failed logins only", if that or the "Both" option is chosed you can do the next part. There will be a failed login message in the errorlog for SQL it will be two lines, 1 is an error code with an error number, a severity, and a state, the other line will be text saying the login failed for this particular user. I need the line with the error number.
You can also verify that the password is indeed correct by using it to login to SQL yourself.
CEWII
June 30, 2011 at 1:00 pm
if your passing a sql login name and password as the connection string to the web.config then its not integrated security . If your application is using a service account (i.e windows account which is authenticated by the LDAP) then you can set it to integrated security.
The login should be mapped to a user in the db , and the user should have only execute permissions on the procs.
You might want to check if the login is disabled or of you have named pipes and remote connections enabled
June 30, 2011 at 3:23 pm
By removing the Integrated Security=false and adding in Trusted_Connection=yes resolved the connection issue.
That seems to all be working fine now. Now on to some encryption questions.. 🙂
There are definitely columns that will require encryption, they contain employee salary information and other highly sensative data.
I agree that the entire db doesn't need to be encrypted, but how do I go about encrypting a column or table?
Is there a tool in SSMS that I can use to do this?
Does SQL handle the encryption/decryption, or will this have to be handled in my front end code?
Thanks for all your help so far!!
June 30, 2011 at 3:37 pm
bubs (6/30/2011)
By removing the Integrated Security=false and adding in Trusted_Connection=yes resolved the connection issue.That seems to all be working fine now. Now on to some encryption questions.. 🙂
There are definitely columns that will require encryption, they contain employee salary information and other highly sensative data.
I agree that the entire db doesn't need to be encrypted, but how do I go about encrypting a column or table?
Is there a tool in SSMS that I can use to do this?
Does SQL handle the encryption/decryption, or will this have to be handled in my front end code?
Thanks for all your help so far!!
First do you want to use certificates or passwords for the encryption, I personally like certs. You can do this in either the front or the backend. Regardless of the path there will be some people who can decrypt the information outside the process. I would suggest starting with something like these:
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/19/637529.aspx
I would only encrypt that which really must be encrypted, keep in mind the encrypted data cannot readily be used in any where clause.
CEWII
June 30, 2011 at 3:40 pm
bubs (6/30/2011)
By removing the Integrated Security=false and adding in Trusted_Connection=yes resolved the connection issue.That seems to all be working fine now. Now on to some encryption questions.. 🙂
There are definitely columns that will require encryption, they contain employee salary information and other highly sensative data.
I agree that the entire db doesn't need to be encrypted, but how do I go about encrypting a column or table?
Is there a tool in SSMS that I can use to do this?
Does SQL handle the encryption/decryption, or will this have to be handled in my front end code?
Thanks for all your help so far!!
You can do it at both layers. Encryption is a pretty broad subject and is probably too large to try to teach across a forum. For example, you could do it via a md5 hash: http://www.codeproject.com/KB/database/xp_md5.aspx. It really depends on where you have more processor available to you to encrypt/decrypt as to where you want it to occur. Most of the time you'll want your app to decrypt however, so that if someone somehow gets their hands on your database server the keys are stored somewhere else completely.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 12, 2011 at 3:07 pm
I read some info on TDE while reading the other articles you posted.
Would you recommend using TDE?
It seems like it would be the easiest solution to put in place since a good portion of the application and database is already in place.
July 12, 2011 at 4:07 pm
Probably not.. TDE protects data-at-rest, while the database is attached to a SQL Server and the server is running ANYONE with sufficient rights can read the data.
As I remember from the discussion this wasn't really the problem you were trying to solve.
CEWII
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply