September 16, 2010 at 1:32 am
Hi all,
is there anyway SQL user can reset his own password if he forget ?
Has any one set this up before ?
any web interface or anything else ?
Any idea ? Any suggestion ?
Basically what I am looking for is to find a way so that as a DBA we don't need to reset password for user if they forget and try to make it automatic as database I am working with have more than 500+ sql user and they forget password all the time in dev and Uat environment.
Any idea, suggestion will be greatlyt appreciated.
Thanks in advance.
Cheers,
September 16, 2010 at 1:41 am
Yeah, you ... can... it's not wise. This is a serious caveat emptor.
However, if you end up doing it anyway, get someone to build out a webpage for you with boxes for the user's sqllogin name and their new password.
You then send down a command using either an SA account, or a call to a proc that runs with SA login (see execute as in create proc), and use the sp_password procedure without an old password in the argument.
See: http://msdn.microsoft.com/en-us/library/aa238870(SQL.80).aspx in BooksOnline
Hang on, I'm not sure this works, reading a few more articles, get back to you.. No, it works, have fun.
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
September 16, 2010 at 2:01 am
Thanks for quick Craig,
How can we ensure that user is trying to reset his own password and not some one else ?
Is there anyway we can verify user before allow him to reset password ?
I am looking for this solution in non prod environment only not for PROD.
September 16, 2010 at 2:01 am
If you do as Craig suggests, you'll need to somehow enforce that they can only change their own passwords. Otherwise some mischievous individual can change his colleague's password, the sa password, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 16, 2010 at 2:02 am
You know how all those websites out there have 'personal questions' and only send resets to pre-set emails in the account and the like? It's because of exactly what you stated above.
If you don't build out a thorough password reset system, you'd have to trust your users more then I usually do. Go do a password reset on your favorite site to get some ideas. 🙂
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
September 16, 2010 at 2:25 am
Assuming you're on a Windows environment, why not use Windows usernames and passwords?
On our DEV, TEST, Staging and UAT boxes, only applications use SQL user accounts, not people.
I get the AD Admins to set up an AD group for say, developers, and that gets an account with appropriate privileges. I don't need to worry about users forgetting passwords anymore. If they can log in to the network, they can login to the SQL server.
Getting them to lock their machines when away from desk is another problem entirely.
DBA (Dogsbody with Bad Attitude)
September 17, 2010 at 1:19 am
Thanks guys,
Sorry for late reply as it seems like we are in different timezone.
@Barry
The problem why we can't use windows credential is that the way application connect to SQL backend need to have SQL login.
@Gail,
How can we do user verification ?
Any suggestion how we can put that personal question idea in to action, Generate random password and send an email ?
Any idea where to start from ?
Thnaks again to everyone.
September 17, 2010 at 1:48 am
Ahh.. cross domain? I have somewhat the same problem since our production servers are not on the same domain as the dev environment and the staging server is on yet another. (makes doing refreshes complicated too).
I had to have the network guys do some acrobatics with firewalls and trusts. 🙂
DBA (Dogsbody with Bad Attitude)
September 17, 2010 at 1:57 am
You'll have to setup a table with all the windows users and their SQL login (+ the server name if you have multiple servers).
When the user uses the website suggested above, you query the table and only show his own sql login, add a reset button and you are done.
That may be a pain to setup the first time but you could upgrade it a bit and use it yourself to add users (and link their windows login to sql login automaticaly).
It might also be cheaper to convert the application to use nt authentication though...
September 17, 2010 at 3:17 am
I removed the user ref of this quote, because I just want to make a general comment on windows authentication ...
...It might also be cheaper to convert the application to use nt authentication though...
And have every ms office user connect and use your data according to the given authorization level ?
quit easy for them if they know the name of your sql instance.
"convert the application" in this case isn't just a connection string modification !
Agreed, with sql2005, using "execute as" with your stored procedures, ... makes things a bit more easy, but are all your applications using only sprocs ?
If not, these users will at least have read authority for (some of) your objects, ....
Give them .vbs, office excell/access :crazy:, and they will use the data at their comfort.
This means they will be able to lock your "regular" applications out because they may lock stuff, perform the most hideous queries, consuming all the instances cpu and IO capacity !
I know it's kind of a political issue, but just wanted to point to some risks.
To get back to the topic:
As already posted by a previous responder, maybe the easiest way is to document your sqluser vs windows account or email address to be able to just provide a web page where they can request a new password. Then send an email to the documented address, providing another web page and a key that only remains active for a little period of time.
Remember to keep track of who requested what and when (have your web page provide tcp\ip address, the windows login, ...) to report (ab)use !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 17, 2010 at 3:34 am
ALZDBA (9/17/2010)
I removed the user ref of this quote, because I just want to make a general comment on windows authentication ......It might also be cheaper to convert the application to use nt authentication though...
And have every ms office user connect and use your data according to the given authorization level ?
quit easy for them if they know the name of your sql instance.
"convert the application" in this case isn't just a connection string modification !
They'll have the exact same rights if they connect using a sql user...
Even worse, a user who shouldn't have the rights to access the db anymore and who happen to know/remember a login and password can still have access to the DB (I.E. someone who changed service), while the nt groups he belong to will be changed.
Even even worse, if you have an audit you'll only know which sql user made an update to your db and no way to know who is really behind it.
Also true if you are trying to find out who is killing your server with this massive query (although you still have an IP to help you here).
If you have security as a priority, don't use a sql user, stick to nt auth and let the windows security team do their job.
If you want to keep user connecting using any application they want, use an application login or a login trigger in SQL 2008.
September 17, 2010 at 3:44 am
Oliiii (9/17/2010)
ALZDBA (9/17/2010)
I removed the user ref of this quote, because I just want to make a general comment on windows authentication ......It might also be cheaper to convert the application to use nt authentication though...
And have every ms office user connect and use your data according to the given authorization level ?
quit easy for them if they know the name of your sql instance.
"convert the application" in this case isn't just a connection string modification !
They'll have the exact same rights if they connect using a sql user...
Even worse, a user who shouldn't have the rights to access the db anymore and who happen to know/remember a login and password can still have access to the DB (I.E. someone who changed service), while the nt groups he belong to will be changed.
Even even worse, if you have an audit you'll only know which sql user made an update to your db and no way to know who is really behind it.
Also true if you are trying to find out who is killing your server with this massive query (although you still have an IP to help you here).
If you have security as a priority, don't use a sql user, stick to nt auth and let the windows security team do their job.
If you want to keep user connecting using any application they want, use an application login or a login trigger in SQL 2008.
And that is very true indeed.
In many cases a sqlusers scope is restriced to a certain db ( altough even that statement is as relative as it gets )
Point is : Security is way more that just grant access.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply