Problem:

You want to create a stored procedure in SQL Server that establishes a linked server connection, performs a query against the linked server, and then tears down the connection. However, you don’t want to store the credentials in the stored procedure itself as it would require updating the stored procedure every time the password changes. How can you securely store the credentials in a way that makes them easier to change?

Solution:

If you are using SQL Server 2005 or above, you can utilize SQL Server’s built-in encryption to store the credentials in an encrypted form and then decrypt them for use in establishing the linked server connection.

First, you need to ensure that the necessary keys are in place. This involves creating a database master key for encryption, a certificate encrypted by the database master key, and a symmetric key encrypted by the certificate. Here is an example code snippet:

USE YourDatabaseName;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourMasterKeyPassword';
GO

CREATE CERTIFICATE EncryptPasswordCertificate
WITH SUBJECT = 'Asymmetric Encryption for Password via Certificate';
GO

CREATE SYMMETRIC KEY EncryptPasswordSymmKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE EncryptPasswordCertificate;
GO

Next, you need to create a table to store the credentials:

CREATE TABLE ForLinkedServer (Username VARBINARY(128), UserPWD VARBINARY(128));
GO

After that, you can create the stored procedures to set and retrieve the credentials:

CREATE PROC dbo.usp_UpdateLinkedServerCredentials
  @Username NVARCHAR(256),
  @UserPWD NVARCHAR(256)
AS
BEGIN
  SET NOCOUNT ON;

  OPEN SYMMETRIC KEY EncryptPasswordSymmKey
  DECRYPTION BY CERTIFICATE EncryptPasswordCertificate;

  DELETE FROM dbo.ForLinkedServer;

  INSERT INTO dbo.ForLinkedServer (Username, UserPWD)
  VALUES (ENCRYPTBYKEY(KEY_GUID('EncryptPasswordSymmKey'), @Username), ENCRYPTBYKEY(KEY_GUID('EncryptPasswordSymmKey'), @UserPWD));

  CLOSE SYMMETRIC KEY EncryptPasswordSymmKey;
END;
GO

CREATE PROC dbo.usp_GetLinkedServerCredentials
  @Username NVARCHAR(256) OUTPUT,
  @UserPWD NVARCHAR(256) OUTPUT
AS
BEGIN
  SELECT
    @Username = CONVERT(NVARCHAR, DECRYPTBYKEYAUTOCERT(CERT_ID('EncryptPasswordCertificate'),NULL, UserName)),
    @UserPWD = CONVERT(NVARCHAR, DECRYPTBYKEYAUTOCERT(CERT_ID('EncryptPasswordCertificate'),NULL, UserPWD))
  FROM dbo.ForLinkedServer;
END;
GO

With the credentials stored and the retrieval procedure in place, you can now use them to establish the linked server connection. Here is an example code snippet:

EXEC dbo.usp_UpdateLinkedServerCredentials @Username = 'YourLinkedServerUsername',
@UserPWD = 'YourLinkedServerPassword';
GO

DECLARE @Username NVARCHAR(256);
DECLARE @UserPWD NVARCHAR(256);

EXEC dbo.usp_GetLinkedServerCredentials @Username OUTPUT, @UserPWD OUTPUT;

EXEC sys.sp_addlinkedserver @server = 'YourLinkedServerName',
@srvproduct = N'SQL Server';
GO

Finally, you can create the stored procedure that performs the query against the linked server. Make sure to wrap the query using sp_executesql to ensure that the credentials are used correctly. Here is an example code snippet:

CREATE PROC dbo.usp_GetDatabasesFromRemoteServer
AS
BEGIN

  IF NOT EXISTS(SELECT name FROM sys.servers WHERE name = 'YourLinkedServerName')
    BEGIN
      EXEC sys.sp_addlinkedserver @server = 'YourLinkedServerName', @srvproduct = N'SQL Server';
    END

    DECLARE @Username NVARCHAR(256);
    DECLARE @UserPWD NVARCHAR(256);

    EXEC dbo.usp_GetLinkedServerCredentials @Username OUTPUT, @UserPWD OUTPUT;

    EXEC sys.sp_addlinkedsrvlogin @rmtsrvname = 'YourLinkedServerName', @useself = 'FALSE',
         @locallogin = 'sa', @rmtuser = @Username, @rmtpassword = @UserPWD;

  EXEC sp_executesql N'SELECT [name] AS [Database]
       FROM [YourLinkedServerName].master.sys.databases;'

  EXEC sys.sp_dropserver @server = 'YourLinkedServerName', @droplogins = 'droplogins';
END;
GO

Now, you can execute the stored procedure to retrieve the databases from the remote server:

EXECUTE AS LOGIN = 'sa';
GO

EXEC dbo.usp_GetDatabasesFromRemoteServer;
GO

REVERT;
GO

By following these steps, you can securely store and retrieve credentials for a linked server connection in SQL Server without exposing them in plain text within your stored procedures. This approach allows for easier management of credentials and enhances the security of your database environment.