Free SQL Help for Devs, DBAs, and the Curious

Linked servers provide a robust environment to execute distributed queries against different data sources, be it SQL Server, MySQL, DB2, or Azure SQL. However, managing these linked servers securely is a crucial aspect of any database administration. This blog post will discuss some best practices for managing linked servers securely using different technologies including SQL Server 2012, SQL Server 2014, MySQL 5.6, DB2 10.5, and Azure SQL.

Firstly, one must ensure that they are using appropriate security settings for linked servers. The recommended practice is to use security credentials that have the minimum required permissions on the remote server. For instance, in SQL Server, you can specify remote login and password while creating the linked server. Below is an example of how to create a linked server with security credentials:

EXEC sp_addlinkedserver @server='MySQLServer', @srvproduct='MySQL',
@provider='MSDASQL', @datasrc='MySQLDSN';
EXEC sp_addlinkedsrvlogin 'MySQLServer', 'false', NULL, 'MySQLUser', 'MySQLPwd';

Additionally, it's important to limit the scope of the linked server to the necessary tables or views. This can be achieved by creating views in the local server that reference the necessary tables in the linked server, thereby hiding the complexity and security implications from the end users. Here's how you might create a view in SQL Server that references a table in a linked server:

CREATE VIEW dbo.vw_linked_table AS
SELECT * FROM LinkedServerName.DBName.dbo.TableName;

Furthermore, for Azure SQL, the firewall settings of the Azure SQL server should be appropriately configured to only allow connections from the necessary IP addresses. This can be done in the Azure portal under the settings of the Azure SQL server.

In the case of DB2 servers, it's recommended to use SSL encryption for data transmitted between the local and linked server. This can be configured in the DB2 server settings. The DB2 client's SSL settings should also be configured to validate the server certificate. The following is an example of how to configure SSL in DB2:

UPDATE DATABASE CONFIGURATION USING SSL_CLNT_KEYDB /path/to/keydb
SSL_CLNT_STASH /path/to/stash;

In conclusion, managing linked servers securely involves using appropriate security credentials, limiting the scope of the linked server, and using encryption where possible. Regardless of the technologies used, these principles will help maintain the security of your linked servers.