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

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

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.

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

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?

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

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.

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

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.

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.