Authenticate SQL login programmatically

[octothorpe]

I'm hoping there is a way to verify a user's password (SQL Server account) within SQL without logging in as that user. Instead, I'd like to connect with a pre-established application account (to take maximum advantage of connection pooling), then impersonate the user.

Example:

declare @user varchar(20)
declare @password varchar(20)

set @user = 'test'
set @password = 'goofball'
  
declare @token varbinary(100)
declare @disabled int
declare @authenticated int

set @disabled = ( select [is_disabled] from sys.server_principals where [sid] = suser_sid( @user ) )

 -- ********* PERFORM USER/PASSWORD CHECK HERE *********
set @authenticated = 1

if ( @disabled = 0 and @authenticated = 1 )
begin
  
  execute as user = @user with cookie into @token

  -- do some stuff as that user

  select suser_name() + ' did some stuff.'
      
  revert with cookie = @token
    
end

else begin select 'failure!'end

Is there any way to check the supplied credentials that would work with this method? Note that in the scenario I'm dealing with, all users will have a SQL login; Windows logins are not a concern.

Any assistance is appreciated.

 

[Qik3Coder]

You will need to hit the database somehow.

The best i can suggest is a global acct which can run the check for you.

I'd recommend some pre-checks to make sure your values are filled in before sending the request to the database, so as to save a trip or two.

-Sometimes the answer to your question is the hack that works

 

[mrdenny]

At this point you aren't using SQL Authentication you are using authentication which is built within the database and application.

You would need to simply create a User table with the usernames and passwords as well as whatever rights are needed. After the user fills in the username and password in the application and they click submit you connect to the database and run a procedure which validates the username and password. If they username fails the application doesn't proceed, if it does they are allowed to login.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[octothorpe]

Hmm, maybe I wasn't clear. This is using SQL Authentication.

Each of the users are set up in SQL and everything about their rights is tied to their SQL user, which can be impersonated by the application account (execute as). I've been tasked with making the application use a single logon for connection-pooling purposes.

I can get the user impersonated using "execute as", which only requires a little code wrapped around the SQL statements - I just can't figure out how to validate that password while connected to SQL as a different account.

 

[mrdenny]

As far as I know there wouldn't be any way to login and then authenticate against the SQL Server natively. A few possible options that I can see are:

1. Fire xp_cmdshell and run osql or sqlcmd and try to log back into the database using the username and password.
2. Create your own xp which connects back via the SQL Account that you pass in.
3. Create a CLR procedure which does the same thing.
4. Figure out how to query the master.sys.sysxlgns object (which I don't think can be done directly).
5. Use OPENQUERY to log back into the database and check the password.

I'm trying to figure out why logging in with the same account would help with connection pooling. Are the connections to the database from a web server, or other middleware server?

I would say that the model being used may not be the correct one for this application.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[octothorpe]

Are the connections to the database from a web server, or other middleware server?

The connections are coming from a web server. Currently, all the connections are made passing the users' credentials in the database connection string. I've been asked to try getting the middle tier to connect with a single login, but since all the permissions are tied to the SQL user account, that breaks the system.

I can get it to work using "execute as" and just wrapping each of the SQL statements in code similar to the above, and most of the web server's code is passing the user and password as parameters whether the statement needs it or not - so if I can figure out a way to check the password, I can accomplish the goal without writing an entirely new security system.

 

[octothorpe]

Got it.

exec sp_password @password, @password, @user

Normally this is used to set the password, but just like ALTER LOGIN (which it calls), supplying the OLD_PASSWORD argument makes it work just as if the user were changing their own password.

If the correct password is supplied, it succeeds or fails with message 15115 (password can't be changed at the present time due to policy). If an incorrect password is supplied, it fails with message 15151 (do not have permission).

It's not functionality I'd want to depend on for the long-term, but it solves my particular problem in the interim.

 

[jbenson001]

Having one name for a log in and then passing the user credentials is not going to help with connection pooling. If 10 people log in, and they all use the same acct, but with thier own credientials, it still opens 10 connections.