sql-server - SQL Server 存储过程无法正确设置上下文信息

Question

这让我发疯。我的 .NET 应用程序调用存储过程以从数据库中检索存储的散列密码:

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

应用程序验证密码并运行第二个存储过程，该过程在 session 表中创建一行，向应用程序返回一个字符串 (GUID)，并设置 CONTEXT_INFO到字符串的值。

现在，只要应用程序打开一个连接，它就会调用 sSessionStart 并传递它在登录期间收到的字符串。如果连接在新 session 中打开，proc 应该将 CONTEXT_INFO 设置为传递给它的经过验证的字符串值:

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

当我在 SSMS 查询 session 中对此进行模拟时，它一切正常。应用程序遇到问题。

第一部分工作正常:

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

此时一切正常，CONTEXT_INFO 在两个过程中都已正确设置。现在连接已关闭，但应用程序具有从 sStartSession 过程返回的验证字符串。

打开表单时，将创建并打开 SqlConnection。表单运行sStartSession，传递字符串参数:

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

有趣的是...sResult 按预期返回字符串，但立即将 CONTEXT_INFO 设置为 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'

呜呜呜!

Answer 1

简短的回答是所有权链。测试存储过程时，我以 SA 身份登录到 SSMS。我在 SSMS session 下模拟登录，一切正常。使用具有非常有限的安全配置文件的应用程序登录是出现问题的地方。结果是该应用程序在一个功能上缺少 EXECUTE 权限:(