79123694

Date: 2024-10-24 21:50:09
Score: 1
Natty:
Report link

This works for me.

Step 1: Add the link server using MSOLEDBSQL for @provider

EXEC master.dbo.sp_addlinkedserver
@server = N'MYLINKEDSERVER',
@srvproduct=N'mssqlserver',
@provider=N'MSOLEDBSQL', /* Microsoft OLE DB driver for SQL Server */
@datasrc=N'TESTSERVER',
@provstr=N'Encrypt=yes;TrustServerCertificate=yes;'

-- Use "yes" NOT "true"

Step 2: Configure the login mapping with SQL Authentication

EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'MYLINKEDSERVER', -- The Linked Server name created above
@useself = 'False', -- Use False to specify SQL authentication
@locallogin = NULL, -- Use NULL to allow all local logins
@rmtuser = 'TESTREADONLYUSER', -- SQL Server login username
@rmtpassword = 'SECRETPASSWORD'; -- SQL Server login password

Reasons:
  • Whitelisted phrase (-1): works for me
  • Long answer (-0.5):
  • No code block (0.5):
  • User mentioned (1): @provider
  • User mentioned (0): @server
  • User mentioned (0): @rmtsrvname
  • User mentioned (0): @useself
  • User mentioned (0): @locallogin
  • User mentioned (0): @rmtuser
  • User mentioned (0): @rmtpassword
  • Low reputation (1):
Posted by: user2658092