Security Primer Request
-
Hi, all,
I am new to this forum, and this is my maiden post. I still do not know the protocol/etiquette prevailing here, tough I have been browsing the forums for a week now.
I do not know whether I am in the right forum, and if I am not, pls guide me gently.
I need help on SQL Server Security. I am new to SQL Server (just been using it as a big version of Access since Jan) and trying to understand. I am not a DBA, but a VB6 programmer using SQL Server 2000 as my back-end.
At present, I am using a SA to login with a blank password, both on my development network and client sites.
My apps use an ODBC connection with the same SA user and blank pwd to connect the DB.
I find this very unprofessional, and would like to implement two or three differing-level logins (with separate privileges).
I've read an MCSE book on SQL Server 2000 about how to create accounts from SQL Server logins and Windows user accounts and groups, assigning server roles and permissions, and creating application roles.
1. If clients are paying for a licensed copy of SQL Server when installing my app (if they do not already have it installed), and they are entitled to set the sa password, and I want to protect my database on their SQL Server with a different login/password, how can I prevent 'sa' from accessing my db?
2. How can I create a login user / application role with restricted access to my db (this pwd will be disclosed to the client)?
3. Can I use the same ODBC connection to access my db with more than one SQL accounts or do I have to create a separate ODBC connection for each user level?
I'd appreciate any and all help.
Thanks.
GoodGuy
-
Good that you have taken the initial step to secure the server. At first change the password for the sa account and kept it confidential within you and some high level managers don't share this with anybody in the team. Next create a sql level user and grant the user db_owner rights to the database that connects to the application as of now. Use this account to make the connection in the client application. Also read the link so that you understand more about sql security.
http://www.sqlservercentral.com/columnists/bknight/10securingyoursqlserver.asp
https://www.microsoft.com/sql/prodinfo/previousversions/securingsqlserver.mspx
Also download the Microsoft best Practices analyzer and run against your server to know the vulnerabilities and take appropriate actions.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
No. with sa you can have control of all objects in the server. So you need not worry about creating a user and giving owner permission for a particular database.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
You can protect your database if you create it as a separate instance of SQL Server and make "linked servers" for the client to yours... of course, linked servers create some other minor problems but it's one way to do it.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I hope you are little confused. I have already said that create a user and give the user db_owner rights in the database being used by the application so that, the user only has access to the database and not the whole server. Change the password of sa and keep it confidential.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Dear Sugesh,
I am sorry for bothering all of you on my first post.
FYI, I did understand you (very well); but my question is (and here I think I am misunderstood), I am not a DBA like most SQL users, but a software developer using SQL Server DB for my VB6 apps.
Some of my clients install SQL Server for the first time when installing my apps, others already have it installed on their networks for many of their other apps.
Either way, my interaction with a client's SQL Server is limted to creating my DB and DSN/ODBC connections. I respect the Client's right to keep the 'sa' password to themselves. But I also need to protect my DB from all others, ie to give my users only restricted rights to the various db objects.
With your sugegstion, I can secure the SQL server in my development lab. I need to secure my DB (and my DB alone) on the client side.
Please assist.
Thank you.
-
Let me first say what i have understood from your question. Correct me if i am wrong.
1. You have a 3rd party application that installs sql server and creates a database and uses a DSN to access the application.
2. The sa password is held by the vendor.
3. You want to restrict only the database access to the users.
If this is your requirement have my answers below.
1. You can create a user id and give access to the database to that user and no more privilege and reconfiggure the DSN to use that id that yu have created now.
2. Ask the vendor to change the password of sa and keep safely with him since with sa you can perform any task within sql server.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
And friend you are not bothering me. Its my pleasure to answer questions to users in this forum so keep questioning until youget your answers that really clarifies you.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Sugesh, thanks for the kind words.
(FYI, I am a hyperactive member on VBCity, and my experience there normally makes me very nervous when venturing to other forums, because the leaders and members there are so cordial, so friendly and so helpful, that it is almost certain that it is the friendliest site on the net. But now I know there are others too.)
Re yor understanding:
No, it is not a third party app.
I am (or my company is) the developer and vendor.
The client is my (company's) customer.
He must have SQL Server installed to run my app.
I do not mind him keeping 'sa' password as it is his licence.
I also do not want him to tamper with my schema.
If I create a second instance of SQL Server at my client's site and change the 'sa' password for that instance only, will it serve my purpose?
-
So it is as easy all that you wanted is the user should not use the database from the backend and use only the application for anything. As said change the sa and keep it yourself or anyother secured person. Create a SQL user and use that to configure the DSN for the application don't let the user know the credentails which is used to configure the DSN. If you want to make it more restricitive what i suggest instead of giving db_owner permission to the application database specify select, insert,update,delete permissions to user table and views and specify execute permission to functions and procedures.
Btw, whats your good name?
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Thanks again for your indefatigable hospitality, may God bless you.
I understood your solution since yesterday, but I do not think I am making much sense to you.
Your solution is good for use inside the development lab.
At client sites, I can NOT usurp the 'sa' password.
So how do I prevent clients from tampering with my DB?
-
Hi Goodguy,
In short, you can't. Sorry for the bad news...
Instead what other vendors do is keep a hash of their data structures and void any support if anything has changed. It's built in their contract.
So if you provide an upgrade to your application, the upgrade will check for the database integrity and validate the hash of its internal objects hasn't changed. If they have, the database has been tampered with.
Hope this helps.
Herve
Herve Roggero
hroggero@pynlogic.com
MCDBA, MCSE, MCSD
SQL Server Database Proxy/Firewall and Auditing -
Thanks, Herve.
I'd like to learn more about the hashing process and how to implement it.
Also, the reason I want to protect my DB is that I do not want anyone, including the client's 'sa' login to view some columns in my DB tables, leave alone changing their values and/or the schema.
One of them is a password column and the other is a table containing the client data.
Don't ask me why, but the policy of this company is to hard-code client data in every DB (no app-side changes). Also, there is a phobia for using encryption on password columns.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply