March 2, 2002 at 9:25 am
When I distribute my application to end users I want to keep the database for my application secure from any prying eyes, including the SA. Is this possible?
I think it would go something like this:
1) I would remove the SA/DBO user from Users on my development machine and add my own User.
2) I would detach the database and install to the users computer, then re-attach the database (I have the code to do this).
3) I need to create a new login for use only with my database (I've seen the code to do this here http://www.sqlservercentral.com/columnists/awarren/loginsusersandrolesgettingstarted.asp)
4) Same link shows how to add a role (not clear on why I need to do this; seems just to complicate things) and attach the user to the role.
5) Call sp_change_users_login to link the new login to my database (I need code to do this from VB).
What am I missing? Under this scenario could their SA simply add themselves as a user/owner to my database and see all my proprietary data structures?
Thanks!
-Ted
March 2, 2002 at 10:40 am
This gets asked regularly. There is no way to secure the database from sa.
Steve Jones
March 2, 2002 at 6:31 pm
I am with Steve, SA has ultimate rights and there is no way to prevent this as that was what it is for.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 3, 2002 at 9:25 pm
I'm part of source selection for a password reset project which is looking to keep passwords in synch between NT and the mainframe. Pretty much all of the vendors encrypt the data within their application and store it in that fashion within SQL Server. There aren't really any other options and if I remember right, Steve has written an article on the subject.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
March 4, 2002 at 7:48 am
Is it possible to keep the data structure (tables, fieldnames, etc.) hidden from SA using encryption?
Thanks,
-Ted
March 4, 2002 at 7:51 am
No. This information is going to appear unencrypted in sysobjects and syscolumns and the Information Schema views. You could potentially create nonsensical table and column names that would not give the sa any idea about what's going on... however, it makes it very hard to support when an issue develops.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
March 4, 2002 at 9:30 am
Unless you keep really good documentation.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 4, 2002 at 9:39 am
Not to prop myself, but I think this is a good summary:
http://www.sqlservercentral.com/columnists/sjones/wp_encryption.asp
Also another view:
http://www.sqlservercentral.com/columnists/NWeicher/securityworstpracticeneil.asp
Steve Jones
March 4, 2002 at 9:49 am
Yes, that's the article I was speaking about by you, Steve. And I still will agree to disagree with Neil on his. It goes back to that inherent trust concept...
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
March 4, 2002 at 9:58 am
99% of my users are on laptops without any other SQL Server applications. So I want to install MSDE and change the SA password via SQLDMO. Is this the right way (code borrowed from this website)?
Dim oServer As SQLDMO.SQLServer
Dim oLogin As SQLDMO.Login
Dim oUser As SQLDMO.User
'get a server object using a trusted connection
Set oServer = New SQLDMO.SQLServer
With oServer
.LoginSecure = True
.Connect
End With
'create a login object and populate it
Set oLogin = New SQLDMO.Login
With oLogin
.Name = "SA"
.SetPassword "", "newpassword"
.Database = "master"
End With
'clean up
Set oLogin = Nothing
oServer.Disconnect
Set oServer = Nothing
BTW, you guys should get paid by Microsoft for the service you are providing here!
March 4, 2002 at 10:24 am
March 4, 2002 at 5:12 pm
Almost. You're treating the login as if it were a new one, to get it work you need to reference the one that already exists, like this:
Set oLogin = oServer.Logins("SA")
Then remove the .name="SA" line, will cause an error.
Alternatively you could just execute sp_password. I like DMO but it's good to know your options!
I have some concerns about trying to prevent the user from accessing the db with sa rights. To be thorough you'd have to remove the NT\Built in group. Would you always be installing a new instance or using an existing one? If its a new one you have to be sure to qualify it when you connect to the server. If its an existing one no one is going to appreciate you changing the sa password.
More importantly to me anyway, one of the reasons SQL is successful is that DBA'd can and do access both the data and stored procedures/triggers to enhance performance, add more functionality, etc. I'll grant that you're probably going to be installing this in an environment that doesn't have a DBA, or the DBA won't know/care about it! If you've got (or the customer will enter) proprietary data then I think you should consider encryption - nothing else will stop someone from just moving the mdf to a different server and accessing the data.
Andy
March 6, 2002 at 7:06 am
I decided to take your advice and add a new user (via SQLDMO) rather than modify the SA password (as deftly demonstrated in this article http://www.sqlservercentral.com/columnists/awarren/loginsusersandrolesgettingstarted.asp)
However, after running that module the new user ends up without any rights to my database. Is there an easy way to add all rights at once, or do I have to do it a table at a time (I have over 100 tables)? They also need rights to CREATE and ALTER tables; I shell out to SQL-DMO to apply the latest database upgrades.
Thanks for all your help.
March 6, 2002 at 9:23 am
If its all or nothing you can use the datareader/datawriter roles. Other than that yes you'd have to do it one at a time, easy enough to do a loop to do the grants. You can also add them to the dbowner or maybe the ddl role to let them do the create/alter.
Andy
March 6, 2002 at 11:14 am
use a role!!!!!! don't grant rights to users!!!
http://www.sqlservercentral.com/columnists/sjones/wp_userrights.asp
Steve Jones
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply