Validating user's password after connection

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Validating user's password after connection

Author

Topic

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-09-03 : 13:42:28

Hiya,
How can a user's SQL Server password be validated after the user has been connected properly?
I am locking the application after n minutes of idling, and the user has to reenter his/her password to unlock the screen. Please Note: The user's connection is NOT getting disconnected, only the screen locks up. Am using SQL Server authentication, VB6, and SQL Server 7.

Sarah Berger MCSD

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-09-03 : 14:03:45

Just try to establish another connection with their credentials. If it works, close it and continue.

Jonathan
{0}

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-09-03 : 14:21:25

How about if they have any pending work? E.g. they are running a long report, this will use client and server resources, but doesn't require user interaction, and may safely freeze the screen, but disconnecting will stop the data interchange between the workstation and server.

Sarah Berger MCSD

EDITED: Sorry, I just realized you wrote ANOTHER, not that the original connection should be closed and reopened.

Edited by - simondeutsch on 09/03/2002 14:22:34

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-09-03 : 14:23:19

Could you not just open a new connection - if it succeeds then you know the password was
correct and close it. Otherwise you're in the realm of undocumented and unsupported procedures
like pwdcompare() e.g.


use master
go
create procedure sp_validate_pass @uname varchar(100),
                                  @pass varchar(100),
                                  @match int OUTPUT
as
set nocount on

select @match = 0        
select @match = pwdcompare(@pass,(select cast(password as varbinary(256))
                                  from master.dbo.syslogins where name = @uname))

return
go

However you'd be passing the username and password in clear text. Opening a new connection
would probably the easiest thing to do.

HTH
Jasper Smith

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-09-03 : 15:10:45

What I'm now doing now is using Persist Security Info=True, so the UID and PWD get saved in the Connectionstring, and then I parse it out by using the Instr and Mid VB functions.

Sarah Berger MCSD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources