SQL Authd users changing passwords?

Question

SQL Authd users changing passwords?

SQLBill

SSC Guru

Points: 51440
More actions
June 17, 2004 at 1:00 pm

#85298

For those of you who use SQL Server authenication for your users:
What method do you use to allow your users to change their passwords?
My users have to come to my facility and change them (via Enterprise Manager). They don't have access to any of the client tools and only use Crystal Reports to access the database.
I am working on creating a Microsoft Access form that will allow them to change their password themselves.
But I got curious how others might be handling this issue.
-SQLBill

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

EdH334 SSCrazy Points: 2857 More actions · Answer 1

Hi SQLBill,

I have a web app which allows super users (application administrators) to manager SQL Server logins on-line and standard users to modify their own passwords.

My ASP page calls sp_password to change a users password, e.g.:

EXEC sp_password 'myoldpassword', 'mynewpassword'

However you need to be careful when sending this data to the server as the password is submitted in raw-text. In my case I use secure sockets, but you may wish to use strong encryption on your ODBC connection or set Use Encryption for Data to True on an Access project's connection.

One more note, I don't allow the creation of super users or sysadmins. They must always be created in SQL EM.

SQLBill SSC Guru Points: 51440 More actions · Answer 2

Ed,

Thank you for your input. My program won't allow setting of permissions. That's my job. I just want to allow regular users to change their passwords from a remote location.

Good point about the encryption. Thanks.

-SQLBill

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

I would go with the ASP page approach already mentioned. The web page can logon to SQL Server with the credentials provided by the user (if they are valid) and then execute the sp_password to change the password. All your remote users would need is a web browser. Also, if you're worried about passwords in plaintext over the network, apply a certificate to the web server (you can generate one using certificate services and pass out the CA server certificate for your clients if you don't already have one) and only a connection to the web page via HTTPS.

K. Brian Kelley
@kbriankelley

SQLBill SSC Guru Points: 51440 More actions · Answer 4

Brian,

I don't use ASP, where can I learn more about programming with ASP?

That might be an option, but I'll have to pass it by my security people. Applications have to be approved before they can be used on our system (and that can take months). MS Access is approved so my form will work. The ASP suggestion should work also since it just uses a browser (I'm guessing from what you said) and IE is an approved software.

-SQLBill

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

There are a slew of books, most of them discounted now since ASP.NET is Microsoft's pushed platform. Sites like the following are great as well:

http://www.4guysfromrolla.com/

If you can write VBScript or JScript (JavaScript), you can write ASP.

K. Brian Kelley
@kbriankelley

EdH334 SSCrazy Points: 2857 More actions · Answer 6

Hi SQLBill,

You should be able to execute sp_password from Access in VBA by creating an ADO command object with parameters for the old and new passwords. These can then be set using vales from a form. Alternatively if using DAO, create a querydef and set the SQL to the T-SQL EXEC command text.

You can also set the Input Mask of the textbox to Password which will disguise the typed characters.

Ed

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 7

If you are working in a Windows environment, consider using Windows Authentication in SQL Server.

Windows authentication allows users to connect with the windows login credentials, so whenever they change their windows logon password, it will "change" in sql server. You'll never need to change a SQL Server password again!

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_47u6.asp

Here's a hot tip though, use NT / Windows User Groups, and give the group permission to SQL Server. Then your IT guys can just add whoever needs access to SQL Server to the User Group. Again, less work for the DBA!

Julian Kuiters
juliankuiters.id.au

SQLBill SSC Guru Points: 51440 More actions · Answer 8

Julian,

Good point, I am aware of that. Your pointing it out showed me that in my original post, I forgot to add:

Windows Authentication is not an option for these users. They are not on a trusted domain and we are not allowed to create a trusted domain between the two sites.

So SQL Server Authentication is the only option.

-SQLBill

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 9

Ouch.

I'm not familiar with Crystal reports, so i'm not sure if you can build a custom form in there or not. Putting the 'change password' in the same front-end app that everyone is using now would be the best idea.

Asides from that Access will be easier and faster for you to setup, rather than a ASP / web app. ASP is easy, it's the locking down of the webserver that is difficult, and by default they are extremely vunerable to hacking and viruses (just have a look at the huge number of hotfixes issued for IIS vs hotfixes for SQL Server).

Julian Kuiters
juliankuiters.id.au