Início / dba / Perguntas / 20250
Accepted
Filip De Vos
Asked: 2012-07-03 03:18:06 +0800 CST

O que pode fazer com que um login criado a partir de um certificado não seja visível nos metadados

  • 772

Estou tentando criar um login e conceder a ele direitos de execução e montagem insegura, mas a verificação em server_principals não retorna o login quando ele já existe. O script funciona perfeitamente bem no meu servidor de desenvolvimento local, mas falha em nossos servidores de teste:

  • Meu servidor dev: Sql Server 2008 R2 SP1 --> funciona ok
  • Servidor remoto1: SQL Server 2005 SP4 --> erro
  • Servidor remoto2: Sql Server 2008 R2 RTM --> erro

Meu usuário é um membro da função sysadmin.

O script criará um certificado (o negócio real extrai o certificado de um assembly .net), verificará se o login existe e, caso contrário, criará o login e concederá direitos de assembly inseguros.

declare @cert_name sysname
      , @safe_cert_name sysname
      , @trust_level nvarchar(20)
      , @login_name sysname

select @cert_name = N'Cert01'
     , @trust_level = N'unsafe'
     , @login_name = QUOTENAME(@cert_name + N'Login')

select @safe_cert_name = QUOTENAME(@cert_name)

IF NOT EXISTS(SELECT * FROM sys.certificates WHERE name = @cert_name)
  BEGIN
    EXEC (N'USE master;
       CREATE CERTIFICATE ' + @safe_cert_name + N' 
       ENCRYPTION BY PASSWORD = ''omg00001111;!@#$%^&''
       WITH SUBJECT = ''Test Certificate you can drop me'', 
       EXPIRY_DATE = ''20120801'';')
   END


IF NOT EXISTS(SELECT * FROM master.sys.server_principals WHERE name = @cert_name + 'Login')
  BEGIN
    PRINT N'Creating login ' + @login_name + ' from certificate ' + @safe_cert_name + ''
    EXEC('USE master;
          CREATE LOGIN ' + @login_name + ' FROM CERTIFICATE ' + @safe_cert_name + ';
          GRANT ' + @trust_level + ' ASSEMBLY TO ' + @login_name + ';')
  END

O erro retornado por este script é o seguinte:

Creating login [Cert01Login] from certificate [Cert01]
Msg 15025, Level 16, State 2, Line 2
The server principal 'Cert01Login' already exists.
Msg 15151, Level 16, State 1, Line 3
Cannot find the login 'Cert01Login', because it does not exist or you do not have permission.

Como meu usuário é administrador do sistema em todos os servidores, estou realmente intrigado por que isso está se comportando assim (a menos que seja um bug corrigido no SQL 2008 R2 SP1).

Editar: nomes de objetos agrupados em "Quotename"

Edit: Adicionado conteúdo de server_principals

SELECT name 
  FROM sys.server_principals 
 ORDER BY name

name
=========================================
##MS_AgentSigningCertificate##
##MS_PolicyEventProcessingLogin##
##MS_PolicySigningCertificate##
##MS_PolicyTsqlExecutionLogin##
##MS_SQLAuthenticatorCertificate##
##MS_SQLReplicationSigningCertificate##
##MS_SQLResourceSigningCertificate##
##MS_SmoExtendedSigningCertificate##
OURDOMAIN\ACR
...
OURDOMAIN\MYUSER
...
OURDOMAIN\ZVA
NT AUTHORITY\SYSTEM
NT SERVICE\MSSQLSERVER
NT SERVICE\SQLSERVERAGENT
SQL_Linker
archiving
bulkadmin
dbcreator
diskadmin
processadmin
public
sa
securityadmin
serveradmin
setupadmin
sysadmin

Editar: saída adicionada de sp_helpsrvrolemember

exec sp_helpsrvrolemember 'sysadmin'

ServerRole  MemberName                MemberSID
==========  ===========               =========
sysadmin    sa                        0x01
sysadmin    NT AUTHORITY\SYSTEM       0x010100000000000512000000
sysadmin    NT SERVICE\MSSQLSERVER    0x010600000000000550000000E20F4FE7B15874E48E19026478C2DC9AC307B83E
sysadmin    NT SERVICE\SQLSERVERAGENT 0x010600000000000550000000DCA88F14B79FD47A992A3D8943F829A726066357
sysadmin    OURDOMAIN\ADMINS          0x0105000000000005150000003301161E3D17782BC63FD75B922C0000
sysadmin    OURDOMAIN\MYUSER          0x0105000000000005150000003301161E3D17782BC63FD75B8B150000
sql-server sql-server-2005

2 respostas

  1. Este código foi executado para mim bem. O único problema que posso suspeitar é que seu servidor diferencia maiúsculas de minúsculas. A EXISTSverificação ficará vazia se você tiver um nome de login, cert01loginmas a criação falhará porque não diferencia maiúsculas de minúsculas. Portanto, você deve garantir que a verificação EXISTS não diferencie maiúsculas de minúsculas, por exemplo

    WHERE name COLLATE Latin1_General_CI_AI = @cert_name + 'Login'
    • 0
  2. Best Answer

    O certificado (que é um certificado Comodo extraído de um assembly) já tinha um login mapeado para ele. Esta é a causa do erro. (No meu servidor local, esse não era o caso).

    Este é o script repro que reproduz o erro:

    declare @cert_name sysname
      , @safe_cert_name sysname
      , @trust_level nvarchar(20)
      , @login_name sysname
      , @login2_name sysname

select @cert_name = N'Cert01'
     , @trust_level = N'unsafe'
     , @login_name = QUOTENAME(@cert_name + N'Login')
     , @login2_name = QUOTENAME(@cert_name + N'Login2')

select @safe_cert_name = QUOTENAME(@cert_name)

IF NOT EXISTS(SELECT * FROM sys.certificates WHERE name = @cert_name)
  BEGIN
    EXEC (N'USE master;
       CREATE CERTIFICATE ' + @safe_cert_name + N' 
       ENCRYPTION BY PASSWORD = ''omg00001111;!@#$%^&''
       WITH SUBJECT = ''Test Certificate you can drop me'', 
       EXPIRY_DATE = ''20120801'';')
   END

IF NOT EXISTS(SELECT * FROM sys.server_principals WHERE name = @login_name)
  BEGIN
    PRINT N'Creating login ' + @login_name + ' from certificate ' + @safe_cert_name + ''
    EXEC('USE master;
          CREATE LOGIN ' + @login_name + ' FROM CERTIFICATE ' + @safe_cert_name + ';
          CREATE LOGIN ' + @login2_name + ' FROM CERTIFICATE ' + @safe_cert_name + ';
          GRANT ' + @trust_level + ' ASSEMBLY TO ' + @login_name + ';')
  END

    Para evitar esse problema no futuro, o script deve ser alterado para:

    IF NOT EXISTS(SELECT * 
                FROM sys.server_principals p 
                JOIN master.sys.certificates c 
                  ON p.sid = c.sid
               WHERE c.name = @cert_name)
  BEGIN
    PRINT N'Creating login ' + @login_name + ' from certificate ' + @safe_cert_name + ''
    EXEC('USE master;
          CREATE LOGIN ' + @login_name + ' FROM CERTIFICATE ' + @safe_cert_name + ';
          GRANT ' + @trust_level + ' ASSEMBLY TO ' + @login_name + ';')
  END

    Eu acho que a mensagem de erro é muito ruim visto que é muito fácil encontrar o login mapeado. Eu registrei o seguinte problema de conexão sobre ele.

    • 0

relate perguntas