0

i have some linkedserver and for it created default maping that mapping list is empty and all goes thrue 'Be made using this seciurity context'

and this works fine - i can querry this linked from any user that have sysadmin role but when i have user that is public only then i get that failed - 'there is no mapping exists for user x'

how can i fix that? is there some special permission that i need to apply ? it is MSSQL 2019 scripted linked is:

EXEC master.dbo.sp_addlinkedserver @server = N'PB01_ENC', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'x.x.x.x,9945'@provstr=N'Encrypt=yes;TrustServerCertificate=yes;'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'PB01_ENC',@useself=N'False',@locallogin=NULL,@rmtuser=N'xxx',@rmtpassword='########'
EXEC master.dbo.sp_serveroption @server=N'PB01_ENC', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'PB01_ENC', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'PB01_ENC', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'PB01_ENC', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'PB01_ENC', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'PB01_ENC', @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'PB01_ENC', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'PB01_ENC', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'PB01_ENC', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'PB01_ENC', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'PB01_ENC', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'PB01_ENC', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'PB01_ENC', @optname=N'remote proc transaction promotion', @optvalue=N'true'

others linked but not encrypted works fine am i missing something here maybe?

even if i add specyfic mapping for this public user - with password or via impersonate - stil the same :/

please advice best regards !

2
  • did you check the user has the same perms in both source and destination?
    – Doug Coats
    Commented Mar 16, 2023 at 18:14
  • Yes perms are ok. if it would be not then it would not work when user is sa group. if i script the linked AS the non sa user then it does not see this part @provstr=N'Encrypt=yes;TrustServerCertificate=yes;' and also does not see the EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'PB01_ENC',@useself=N'False',@locallogin=NULL,@rmtuser=N'xxx',@rmtpassword='########' part . so guess this is the issue here somehow
    – Dorian
    Commented Mar 17, 2023 at 6:06

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.