Certificate issue with execute as owner

• tony28

  SSCertifiable

  Points: 7112

  More actions

  July 13, 2022 at 3:10 pm

  Go to Answer

  #4062802

  Hello,

  I have some issue with execute procedure with new certificate .

  We have inside procedure WITH EXECUTE AS OWNER and is not working and got error The server principal "sa" is not able to access the database "CertDB2" under the current security context.

  If I remove then I can execute without problem.

   

  Problem is that we have another database with another certificate and there is working without problem and I cannot find why, because this is from past.

  please anybody can help with this?

  I will write code here for possible testing ..

  Let me know if you will want more.

  Thanks

  use master
  
  ALTER DATABASE [CertDB1] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
  drop database [CertDB1]
  ALTER DATABASE CertDB2 SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
  dROP database [CertDB2]
  
  --created databases
  EXECUTE AS LOGIN = 'sa'
  CREATE DATABASE [CertDB1]
  CREATE DATABASE [CertDB2]
  
  
  ----create cert in first DB
  use [CertDB1]
  CREATE CERTIFICATE CrossDBCert
  ENCRYPTION BY PASSWORD = 'Privatekey'
  WITH
  SUBJECT = 'CrossDBCert'
  , EXPIRY_DATE = '99991231'
  
  ---get binary for certificate and write to command below
  use [CertDB1]
  
  
  (SELECT CERTENCODED(CERT_ID('CrossDBCert')) AS PublicPortionBinary)
  
  (SELECT CERTPRIVATEKEY
  (
  CERT_ID('CrossDBCert')
  , 'EncryptionPwd' /* Encryption password */, 'Privatekey' /* Private key */) AS PrivateKeyBinary)
  
  
  ---- create certificate on second DB, use binaries from previous select 
  
  use [CertDB2]
  
  declare @sql varchar(max)
  
  CREATE CERTIFICATE CrossDBCert
  FROM BINARY = 0x308202B63082019EA0030201020210363A5841A0267EA9470BB14B5890EF6B300D06092A864886F70D01010B05003016311430120603550403130B43726F73734442436572743020170D3232303731333134353734365A180F39393939313233313030303030305A3016311430120603550403130B43726F737344424365727430820122300D06092A864886F70D01010105000382010F003082010A0282010100CD5A07B753B36FFF74C0F82A428DB67819C310343F5B8106C860399827578EB378E7B62A520BCC7978A391A9019F86F40547136718EF4D97719D0DA6E6F5EF99BF2272F592D61EA2D90DA879DFB0E65D16B221F3D6093D6300FFAFDDBA21B39A69C1DD60F94F6457822104105A47F73F293AA4C19FFC044079D8A1E7F959C0039658E8155FFF2E9B6660BB91713D24BBEF09F14572D79BF4EB31B5763FC6E045A0508F57937FE29622DE993729847F29F75A4C28C6B3012AD7229690065518DB72FA478B57FAA62758A6BE1E8852DBF56CD172AF4A9611642860B60B7A13A292FB90703B6272E2145262C73970D51EF32E015A24629A356DAA3BED23B06063150203010001300D06092A864886F70D01010B0500038201010052F0D0EEF8484889EB8A7928AE0B62B9AAA0A1969656B8832F6D2AF2FB86FEC82D55440E2BE2F95520995DBD32D4484E3E7026B0C89C6D33F218527EEF3A0AEA7B067691A4C6AE13A87E9409EAEA08135E20601CA65E0679C48D34C5E072A5EAAC5796C58FAD749B29CB9AB2C9143FB0D47EA447B60CDD0B8E57ECD546EF167F0A4F857FA9F085342BE7DC7DD5189BF7BB31797F3339A82D18B2805750ED054B13DD0B098981B1C8C65710594376235AB62E141AEC3DD7FF0B185D9AFA981C3F0478B4B3D03B95919AE822BCDE6D19414DF3D05601C69486C23A19DF831C72692A223C71AD4914A77FBFC3F0AE2C71D67B6E3C7E178CEDB760B3AE04C6C555EC
  WITH PRIVATE KEY 
  (
  BINARY = 0x1EF1B5B0000000000100000001000000100000009404000029D83FA30714D5175D95FAD305B24B970702000000A40000A6478DDA6DC5EF03D0B615DADDFAB2C16784CFBDAC547D77C59F7E661E6AFC927876E52E4B861E6BB24F67EFD6693E385F9782285E1418AFCB46F26BC604F347E19B692F578B745BB31BB979FFFC173C15EA0324B7B1215161380750249EB93923EA77C09827776160E625B807AC1798AA4C37E55075C501066B663124707D12D63057FC011BADF744E498154561BF8A580267902FBD3B83A5EBB5796D1107E27AD8E521449CD8274F56629EC54272CAD4A53B5E65E65CB476E6865E1AA3721596F5689A6B7C9732D0727BF7ED20A45B1DDC103109A09AA50A55D1B403CE0B3E3937E236A3DF34E61772D02ECFC334BFD91FD96E2639E36237DC9B6987E6E44911C41552568203E8E2F1194255BC76CF60524238F3E05EBEE75A028392C04FC8A75B874E0D7214C12CC3E5ECFB2B023C4A918BB46CE8E150087E3675861D825FF724B5AD5761BF1906A77EF6969CA1A41C43C3C1255326E374BE0EB6D1A6B5F8CE5283702C917170CE1AF7C24DFEFC620FDA811F74B02CC98D78D7360BC593285F3C2BA63735CF59827A96D4ECD8D72244C6F53B984ED56BFF035C5651F0F280282BF0F7CAE3F6023BFD0F40672FB825AD5FAD2F1BAE76D77FD8CCCD597C507B05C0B7D3C48246950C6566D4F4C63F7664DA01ECD6E92C206D58E40BA72500357DDCFB3FD453F4B373AB396C6E0A615913C77D11148AB7706C5273AECC3E702F68F259B5A16980E7703CC678E9F68DCA5BDF6D7F597966C4ECBB5C996F9A387618960F613D050492AAC888BF4CADAB61744796F353398BA9A4165351D205545DBD1121BF41F84B93F11534BE7A4EAE90465D3AD84B1571207F364E8BEABD1B7237EC5E564592D3C4CD5464DD4580B8104876E6672572118EFAF4A4847ED0360EDBF7C5F42B57698A738BA66C239700F41D5E30D8212C5425CF731F96855D3BF5B466313592F9CC818A79076415841F6DF9EBA13933532FFB3D48D093378E7390F01F0CA1B8F4DF01063857AE8E9FFE9107BD51BF799B8C0927D8E335361ED93E53CED7486513471E5D6A1353D7FC9F93CEBFD70E349664F815EF2DCAEF925A8A5BAB15FB97DF710D85B26505EBAD35C97EE436EE50C40E893EC844F717047180088044E7602E3054122D4726F24EB088EF5BF7D7CDB55D64E8D38C95827A10659C5521C760152A57818F0F4ED01D04D49052C2B3AC3072DE5EC04151D86283A3B43C4C9A4BEC81E05744AE6C730EFB05D1DEFD9A2BE22DE5E34FF0024BBC220A2F0B9F58C32AA50E549132116710B406B6298B4FE6625F1F066EB0F44D504FD8DD38D1F3803C6444DC2169E453585554ED3B5C101DF3C07C65E9E31AB62C778B0B0696AB0F051B0C2528B5E21B9F9CD90E8009E49DEE656619052593CE3265603641C088FDCE3B1C1F82D3F7B2188DFF8AE16C4DF24EFEF5CB53264D89716900C2A132D798C6875FEF690A9DEEB2C55AFE7F81A3B85E0C4EF9C70B7EAC737839D9B3BA57B6836EF77EDE89941B577AC01B78F4729F473A24E5A270F5EBABF9CC3652C503EA51EFD7C167FF4ADA786AAECDF6FFBC50293479EB00F7358685BC17EDC79C58AA71FA87E62AF8AD186AB5A7C915475B343C811E2FA89596B3FAAC1DB58FF44FAF160654502A54E0
  , DECRYPTION BY PASSWORD = 'EncryptionPwd' /* Decrypt using the same password from the previous step */, ENCRYPTION BY PASSWORD = 'Privatekey' /* You can set the same or new private key password */)
  
  
  --create table in DB2
  
  USE [CertDB2]
  CREATE TABLE [dbo].[TableRead](
  [id] [int] NULL,
  [test] [nchar](10) NULL
  ) ON [PRIMARY]
  
  INSERT INTO [TableRead] (id,test) values (1,'test1')
  
  
  --create user from certificate
  
  USE [CertDB2]
  CREATE USER [CrossDBCertUser] FROM CERTIFICATE CrossDBCert
  
  
  use [CertDB2]
  GO
  GRANT SELECT ON [dbo].[TableRead] TO [CrossDBCertUser]
  GO
  
  
  ---create procedure in first DB1 for select to second
  
  USE [CertDB1]
  go
  create or alter PROCEDURE [dbo].[ReadDB2]
  WITH EXECUTE AS OWNER -- remove and add for testing issue
  AS
  BEGIN
  
  
  SELECT  
    SUSER_NAME() AS serverLogin
    , USER_NAME() AS dbUser
    , ORIGINAL_LOGIN() AS originalLogin
  
  
  SELECT
  
  COUNT(*) FROM [CertDB2].dbo.[TableRead]
  
  
  END
  ------------------------------------------------------
  
  --add signature to procedure
  ADD SIGNATURE TO [CertDB1].[dbo].[ReadDB2] BY CERTIFICATE CrossDBCert WITH PASSWORD = 'Privatekey' -- <- Password from PasswordSafe
  
  
  
  
  --create login 
  
  use master
  go
  If not Exists (select loginname from master.dbo.syslogins 
      where name = 'TestCert' )
  BEGIN
  CREATE LOGIN TestCert WITH PASSWORD = 'TestCert'
  END
  
  GO
  USE [CertDB1]
  CREATE USER TestCert FROM LOGIN TestCert
  
  GRANT EXECUTE ON [CertDB1].[dbo].[ReadDB2] TO TestCert
  
  
  --testing
  
  
  USE [CertDB1]
  EXECUTE AS LOGIN = 'TestCert' /* Impersonating a Server principal *//* After impersonation */SELECT 
    SUSER_NAME() AS serverLogin
    , USER_NAME() AS dbUser
    , ORIGINAL_LOGIN() AS originalLogin
  
  EXEC [CertDB1].[dbo].[ReadDB2]
  
  
  
  REVERT -- end the impersonation
  
  
  
  
  
  
  
  

   

   

   

   

  • This topic was modified 2 years, 4 months ago by  tony28.
  • This topic was modified 2 years, 4 months ago by  tony28.

• July 14, 2022 at 7:34 am

  This was removed by the editor as SPAM

• tony28

  SSCertifiable

  Points: 7112

  More actions

  July 14, 2022 at 8:02 am

  Answer

  #4063138

  fixed with

  use [CertDB2]

  GO

  GRANT AUTHENTICATE TO [CrossDBCertUser]

  GO