This is driving me crazy. My .NET application calls a stored proc to retreive a stored hashed password from a database:
ALTER PROCEDURE [dbo].[sGetHashedPW]
-- Add the parameters for the stored procedure here
@UserName varchar(32) = N''
AS
DECLARE @ContextInfo varbinary(128)
BEGIN
SET NOCOUNT ON;
--Set Context Info for this session to Username requested
SELECT @ContextInfo = CAST(@Username AS varbinary(128));
SET CONTEXT_INFO @ContextInfo;
--Return Hashed User Password to Application
SELECT TOP 1 [Password] as PWHash FROM [dbo].[vSecurity] WHERE [UserName] = @UserName;
END
The application verifies the password and runs a second stored procedure which creates a row in a session table, returns a string (GUID) to the application, and sets CONTEXT_INFO to the value of the string.
Now anytime the application opens a connection, it calls sSessionStart and passes the string it received during login. If the connection opens in a new session, the proc SHOULD set CONTEXT_INFO to the verified string value it was passed:
ALTER PROCEDURE [dbo].[sStartSession]
@token varchar(128)=NULL
AS
DECLARE @GUID uniqueidentifier
BEGIN
SET NOCOUNT ON;
IF @token IS NULL --Token not passed from the application
BEGIN
BEGIN TRANSACTION;
BEGIN TRY
SELECT @GUID = personnel_token
FROM t300_Personnel INNER JOIN vSecurity ON t300_Personnel.personnel_user_name = vSecurity.UserName
WHERE personnel_user_name = CAST(CONTEXT_INFO() as varchar(128));
SET @GUID = ISNULL(@GUID,NewID());
INSERT INTO dbo.t900_UserSession
(session_token,session_dbuser,session_id)
OUTPUT CAST(@GUID as varchar(128))
SELECT @GUID, [DB_User], @@SPID
FROM t300_Personnel INNER JOIN
vSecurity ON t300_Personnel.personnel_user_name = vSecurity.UserName
WHERE [UserName] = CAST(CONTEXT_INFO() as varchar(128));
UPDATE dbo.t300_Personnel SET [personnel_token]= @GUID
WHERE [personnel_user_name] = CAST(CONTEXT_INFO() as varchar(128));
--Change Context Info to the GUID
SET CONTEXT_INFO @GUID;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
THROW 51000, N'Session could not be opened',1;
END CATCH
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
END
ELSE --Token was passed from the application
BEGIN
BEGIN TRY
SELECT @GUID = CAST(@token as uniqueidentifier);
INSERT INTO dbo.t900_UserSession
(session_token,session_dbuser,session_id)
SELECT @GUID, DB_User, @@SPID
FROM t300_Personnel INNER JOIN
vSecurity ON t300_Personnel.personnel_user_name = vSecurity.UserName
WHERE personnel_token = @GUID;
IF (@@ROWCOUNT = 0) --Insert failed due to invalid token
SELECT CAST('INVALID TOKEN' as varchar(128));
ELSE --New session was created
BEGIN
SET CONTEXT_INFO @GUID;
SELECT CAST(@GUID as varchar(128));
END
END TRY
BEGIN CATCH
IF (ERROR_NUMBER() = 2627) --Token is valid but session already exists
SELECT CAST(@GUID as varchar(128));
ELSE
THROW;
END CATCH
END
END
When I simulate this in SSMS query sessions it all works fine. The application is having trouble with it.
The first part works fine:
Public Sub Authenticate(username As String, password As String, connString As String)
Using oConn As New SqlConnection(connString)
'Check that connection exists and is open
oConn.Open()
If oConn.State = ConnectionState.Open Then
Dim sqlCmd As New SqlCommand("EXEC dbo.sGetHashedPW N'" & username & "'", oConn)
_pwhash = sqlCmd.ExecuteScalar
_authenticated = EncryptHash.VerifyHash(password, "SHA512", _pwhash)
If _authenticated Then
_username = username
_connString = oConn.ConnectionString
sqlCmd.CommandText = "EXEC dbo.sStartSession"
_hashToken = sqlCmd.ExecuteScalar
Else
clearVariables()
End If
End If
End Using
End Sub
Everything works great at this point, CONTEXT_INFO is set correctly in both procedures. The connection is closed now, but the application has the validation string returned from the sStartSession procedure.
When a form is opened, a SqlConnection is created and opened. The form runs sStartSession, passing the string parameter:
Private Sub frmPersonnel_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
Me.MdiParent = frmMain
'Open the form's connection and ensure the database session is logged
oCN = New SqlConnection(currentUser.ConnectionString)
oCN.Open()
Dim sqlCmd As New SqlCommand("EXEC dbo.sStartSession '" & currentUser.Token & "'", oCN)
Dim sResult = sqlCmd.ExecuteScalar
Here's the funny thing... sResult returns the string as expected, but immediately the CONTEXT_INFO is set to 0x00000:
SELECT
[session_id], [context_info],
CAST([context_info] as uniqueidentifier) as Context,
CAST([context_info] as varchar(128)) as Context2,
[program_name]
FROM
sys.dm_exec_sessions
WHERE
[program_name] = 'this application'
ARGHHHH!
