Snippet Name: Create_Linked_Server_with_Encryption

Tags:

Created Date: 2025-06-16 13:12:48
Last Modified Date: 2025-05-31 10:32:01

Dependencies:

DBA Notes:

Sample Output:

Title: Create Linked Server with Encryption
Author: user
can you help me with teh syntax to create a SLQ Server Linked server to the server "S1WDVSQLDX3.US1.autonation.com\WKDAYDEV,49173", that features the string to enable column encryption

Author: ChatGPT
To create a SQL Server Linked Server to connect to `S1WDVSQLDX3.US1.autonation.com\WKDAYDEV,49173` with column encryption enabled, you need to specify the appropriate provider and connection string. The connection string must include the `Column Encryption Setting=Enabled` parameter.

Here is the T-SQL syntax to create the linked server:

```sql
EXEC sp_addlinkedserver 
    @server = 'LinkedServerName',  -- Replace with your linked server name
    @srvproduct = '', 
    @provider = 'SQLNCLI11', 
    @datasrc = 'S1WDVSQLDX3.US1.autonation.com\WKDAYDEV,49173';

EXEC sp_addlinkedsrvlogin 
    @rmtsrvname = 'LinkedServerName',  -- Replace with your linked server name
    @useself = 'FALSE', 
    @locallogin = NULL, 
    @rmtuser = 'YourRemoteUsername',  -- Replace with the remote login username
    @rmtpassword = 'YourPassword';    -- Replace with the remote login password

-- Set options for column encryption
EXEC sp_serveroption 
    @server = 'LinkedServerName',  -- Replace with your linked server name
    @optname = 'rpc out', 
    @optvalue = 'true';

EXEC sp_serveroption 
    @server = 'LinkedServerName',  -- Replace with your linked server name
    @optname = 'use remote collation', 
    @optvalue = 'true';

-- Include 'Column Encryption Setting=Enabled' in the connection string
EXEC sp_serveroption 
    @server = 'LinkedServerName',  -- Replace with your linked server name
    @optname = 'connect timeout', 
    @optvalue = '30';

-- Adjust the provider string to enable Always Encrypted
EXEC sp_addextendedproperty 
    @name = N'ProviderString', 
    @value = N'Column Encryption Setting=Enabled', 
    @level0type = N'LinkedServer', 
    @level0name = 'LinkedServerName'; -- Replace with your linked server name
```

### Explanation:
1. **`sp_addlinkedserver`**: Configures the linked server. Use `SQLNCLI11` for the SQL Server Native Client provider or adapt it to your environment (e.g., `MSOLEDBSQL` for newer drivers).
2. **`sp_addlinkedsrvlogin`**: Sets the security context for the linked server.
3. **`sp_serveroption`**: Configures linked server-specific options.
4. **`Column Encryption Setting=Enabled`**: This ensures the linked server connection supports Always Encrypted columns.

Replace placeholders (`LinkedServerName`, `YourRemoteUsername`, `YourPassword`) with actual values specific to your environment. Adjust options as necessary for your configuration.

If you encounter any errors or have specific requirements, let me know!

Author: user
only for the last statement, i got this error: Msg 15600, Level 16, State 3, Procedure sp_addextendedproperty, Line 37 [Batch Start Line 30]
An invalid parameter or option was specified for procedure 'sp_addextendedproperty'.


Author: ChatGPT
The `sp_addextendedproperty` procedure is not typically used for setting provider strings or other linked server options directly. Instead, you need to include the `Column Encryption Setting=Enabled` parameter in the `@provstr` (provider string) during the `sp_addlinkedserver` creation itself or by using the GUI in SQL Server Management Studio.

Here is the corrected approach:

### Adjust the `sp_addlinkedserver` Call
You should specify the provider string directly in the `@provstr` parameter of the `sp_addlinkedserver` procedure:

```sql
EXEC sp_addlinkedserver 
    @server = 'LinkedServerName',  -- Replace with your linked server name
    @srvproduct = '', 
    @provider = 'SQLNCLI11', 
    @datasrc = 'S1WDVSQLDX3.US1.autonation.com\WKDAYDEV,49173',
    @provstr = 'Column Encryption Setting=Enabled';
```

### Explanation:
- The `@provstr` argument allows you to provide additional provider-specific properties in the connection string. Here, it includes `Column Encryption Setting=Enabled` for Always Encrypted support.

If your linked server was already created and you need to add the `@provstr` property, you'll need to drop and recreate the linked server. Unfortunately, SQL Server does not support modifying the provider string of an existing linked server. 

Let me know if you encounter further issues!