|
Subject:
|
Passwording a database
|
|
Posted By:
|
x_ray
|
Post Date:
|
1/7/2006 5:38:41 AM
|
Hello all,
i want to know how to apply a password to a database on sql server 2000, note however that i have deployed the database on the network and now i have connection to it though web interface(connection strings...)i need to apply a password so no body could enter to the database on the server but me but i dont want to change a lot in my connection strings, is it possible?
i am new to sql server and i really need help asap, thank you
|
|
Reply By:
|
jbenson001
|
Reply Date:
|
1/8/2006 11:36:37 PM
|
The users will not be able to log on to the server except for your app unless you give them sql client tools (ie. Query Analyzer or Enterprise Manager). If they do not have those or other client tools on their PC, they will not be able to log into the server directly.
Jim
|
|
Reply By:
|
nalla
|
Reply Date:
|
1/9/2006 12:45:28 AM
|
Hi x_ray,
sp_password '@old_password' , '@new_password' , '@login' ]
@old_password - old password
@new_password - new password
@login - login name
Eg:- EXEC sp_password NULL, 'spider', 'sa'
This is how we change the password of 'sa' user to 'spider' which was NULL.
I hope your connection string is not hard coded, so update the connection string's password as well. If your application retrieve the server name, database name , user id , password from the registry or ini file update the registry/ini file's password dalso.
nalla.
|
|
Reply By:
|
David_the_DBA
|
Reply Date:
|
1/9/2006 11:47:31 AM
|
xray:
jbenson001 is generally right in that the users can't logon directly to your SQL Server without tools, knowledge and permissions. However, there is an MS Office Component called MSQuery that is included with Excel that allows users to query databases through ODBC. So even without the SQL Server client tools (like EM and QA) they still can connect if they know how and have permission (i.e. if they know the sa password).
David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com
|
|
Reply By:
|
x_ray
|
Reply Date:
|
1/13/2006 1:42:04 PM
|
ok thanks you jbenson001,nalla and of course David, for all your help.
David, how can i disallow such users using excel to enter the database?
|
|
Reply By:
|
David_the_DBA
|
Reply Date:
|
1/14/2006 9:35:25 AM
|
Have you granted access to any of their domain accounts? Have you created SQL Server Logins for them and given them the login name and password? If not then they can't access your app through EXCEL. They need credentials.
In cases where they have been given such usernames and passwords or access granted to their domain accountsm their are two approaches:
1) lock down through permissions such that they only have access to the stored procedures that they would need to call (as long as the underlying objects are owned by the same owner as the stored procedure then callers of the stored procedure do not need permissions to the underlying objects).
2) Deny the users permissions and give them all to app role and then from within your app call sp_activateapprole to activate the role. (Downside to this is it makes it impossible to do connection pooling.
So what I have seen most shrinkwrap apps do is to connect to the database using SQL authentication with a standard username and password, and then provide some sort of custom authentication to the application.
David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com
|
|
Reply By:
|
x_ray
|
Reply Date:
|
2/4/2006 6:58:13 AM
|
ok thanks david i will try it and inform you. however if need to change the authentication on the server from standard to integrated (NT) what should i do in order not to harming connections to the database available on the server?
thank you all again.
|
|
Reply By:
|
David_the_DBA
|
Reply Date:
|
2/5/2006 7:06:18 PM
|
x ray:
I am not sure I follow your question. Are you looking at changing the server wide setting from Mixed Security to Integrated security? Or just changing how your app connects to the server?
David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com
|
|
Reply By:
|
x_ray
|
Reply Date:
|
2/6/2006 4:07:52 PM
|
i am looking for both,since i have an application accessing the db on the server, however i think i know how to manage that, but some extra professional info is not bad.but first i need some info about changing the security setting from mixed to integrated.
sorry for being greed, and thank you for evrything.
|
|
Reply By:
|
x_ray
|
Reply Date:
|
2/8/2006 11:53:09 AM
|
ok let me explain this further, i have sql server running on a machine, when i installed it i used the sa login with password(ex:123), now my boss is asking to change the password to be integrated, the one for windows.that is one.
Two, i have an application accessing the database(a website actually), i will need to change the connection string,as well as dns object, but not sure how, in order to access the database according to the integrated security on the server.( i am not sure this is the right forum to discuss this, i will try to post it somewhere else, but if you have the answer i will be thankful.)
Help is badly needed.
|
|
Reply By:
|
Hal Levy
|
Reply Date:
|
2/8/2006 3:42:44 PM
|
x_ray,
There are two kind of security in SQL Server- Integrated and Mixed.
Integrated Security means that your SQL server will only accept Domain accounts for security
MIXED means that your SQL server will accept either Domain accounts or "local" accounts created in SQL Server.
Are you saying you want to get rid of Mixed security and move to Integrated Security?
Hal Levy
Please do your own homework.
I am here to help you, not do it for you.
I do not have sample code for anything
|
|
Reply By:
|
x_ray
|
Reply Date:
|
2/10/2006 12:27:25 PM
|
yes, that is what i want to do.and also i am asking if this will affect the connection to the server, and if yes how to solve it?
Sorry for any inconvenience.
|
|
Reply By:
|
Hal Levy
|
Reply Date:
|
2/14/2006 5:53:07 PM
|
x_ray- It will certainly affect the connection to the server. If your using a "sql" security account and need to change to a trusted source. You solve it by changing how you connect to your SQL server.
I don't have SQL access where I am at the moment- but I think you change it by right-clicking on the server in question and going to properties. I think it's a property on one of the sheets there.
Hal Levy
Please do your own homework.
I am here to help you, not do it for you.
I do not have sample code for anything
|
|
Reply By:
|
jbenson001
|
Reply Date:
|
2/14/2006 10:20:07 PM
|
Hal, you are correct. If you right click the server and choose properties, there is a Security tab. You can change the mode there.
Jim
|
|
Reply By:
|
x_ray
|
Reply Date:
|
2/15/2006 3:08:41 PM
|
ok thanks a lot for both of you, me too i dont have access to sql server at the moment, but i will do it once i have a chance.
thanks again.
|
