Programming4us
         
 
 
SQL Server

Protecting SQL Server Data : SCHEMA ARCHITECTURE STRATEGIES - Harnessing Linked Servers

8/12/2011 9:24:33 AM
Linked Servers offer to security administrators, when implementing their schema architecture strategies, an additional layer of separation between sensitive and less sensitive data. Often, certain pieces of sensitive data are required by several different applications or departments within an organization. However, rather than store this data in multiple places, with the attendant security risk that this entails, it may be desirable to store the data in one place only, on a separate physical server, to which access can be strictly regulated. This server may be in a separate geographic location from the servers that contain information of lower sensitivity.

So, for example, rather than having a customer's federal tax identification number stored in multiple systems, with "varying" levels of security applied in each case, you can place it on a central server that can be securely accessed by all systems that need this information. This "centralized" architecture has several advantages:

  • It ensures that the data is consistently protected and that its access is easily managed.

  • It reduces the redundancy of sensitive data throughout the enterprise

  • It provides benefits for disaster recovery due to the ability for a linked server to reside in a separate physical location.

  • It provides a separation in the administration responsibilities, which reduces the risk of the DBA being the source of disclosure.

Access to the SQL Server instance containing the sensitive data, from other servers in the system, is enabled using Linked Servers.

NOTE

While it's optimal that the linked instance resides on a separate physical server, it can also reside on the same physical server in another SQL Server instance.

This feature of SQL Server allows commands from one instance of SQL Server to be executed against another instance through an OLE DB (Object Linking Embedding Database) provider, as shown in Figure 1.

Figure 1. Linked servers.

The linked server does not necessarily need to house a SQL Server instance. There are OLE DB providers that allow you to link to an Oracle database, DB2, XML, or MS Access, and various other database platforms are supported through Open Database Connectivity (ODBC) drivers.

Implementing Linked Servers

In our sample HomeLending database, we will use a linked server to access credit report data for our borrowers. Rather than storing the credit report details, which are packed with account numbers, balances, and federal tax identification numbers, alongside the loan application data, they will be stored in a separate physical server (Server2), with highly restricted access, as shown in Figure 2.

Figure 2. Foreign key to a table on a linked server.

We'll allow only the interface with the credit bureau to write data and the users that gain access to this linked server will be carefully defined and granted only read permissions. Therefore, through the use of linked servers the credit report details in our sample database are stored securely in a server that is dedicated to that sensitive data. Its reference to any borrower information that would be identifiable is through a foreign key that is stored in our primary server, referenced in Figure 3-11 as SERVER1\SQLINSTANCEA.

Linked servers are implemented using the sp_addlinkedserver system stored procedure, the syntax of which is as follows:

sp_addlinkedserver [Server Name], [Product Name],
[Provider Name], [Data Source],
[Location], [Provider String],
[Catalog]

This system stored procedure's arguments are as follows:

  • Server Name: The textual reference to the linked server that is being added. If using the native SQL Server OLE DB provider the server and instance name of the target database is the value that should be entered.

  • Product Name: A descriptor of the database product that is being connected. For example: "SQL Server" or "Oracle".

  • Provider Name: The unique programmatic identifier of the OLE DB provider that is being used. This can be obtained using the provider name value from the results of executing the extended stored procedure xp_enum_oledb_providers. If this argument is omitted, it will default to the native SQL Server OLE DB provider.

  • Data Source: The textual reference to the instance that is accessed through the linked server. The value of this argument is dependent upon the provider used. If the native SQL Server OLD DB provider is used this argument should be omitted. This argument is passed as a property to the OLE DB provider.

  • Location: The textual reference to the location of the linked server database. This argument is passed as a property to the OLE DB provider.

  • Provider String: The textual reference to the connection string to the instance that is being linked. This argument is passed as a property to the OLE DB provider.

  • Catalog: The textual reference to the specific database that is being accessed through the linked server. This argument is passed as a property to the OLE DB provider.

Not all of the arguments are applicable to all OLE DB providers. In the case of the native SQL Server OLE DB provider, simply providing the server name and product is sufficient.

Once a linked server has been created, its logins will need to be established to provide access to it. This is accomplished by executing the sp_addlinkedsrvlogin system stored procedure, the syntax of which is as follows:

sp_addlinkedsrvlogin [Linked Server Name], [Use Self],
[Local Login], [Remote User],
[Remote Password]

This system stored procedure's arguments are:

  • Remote Server Name: The textual reference to the linked server for which the login is being created.

  • Use Self: A value of true indicates that the users connect to the linked server using the credentials that are used on the primary server. A value of false indicates that the credentials that are used to login to the linked server are different than the credentials used to login to the primary server.

  • Local Login: The SQL Login or Windows Login that is used to gain access to the linked server.

  • Remote User: The SQL Login used to gain access to the linked server, if the Use Self argument is set to false.

  • Remote Password: The password used to gain access to the linked server if the Use Self argument is set to false. Please note that this argument is passed to the linked server in plain text.

  • For the benefit of the HomeLending database, which resides on SERVER1\SQLINSTANCEA, we will create a linked server, referenced as SERVER2\SQLINSTANCEB, using the native SQL Server OLE DB provider, in which the credit report data will be stored. The server login on the linked server will be the same as that used on our primary server, as shown in Listing 1.

Listing 1. Implementing a linked server.

You may notice that the script used to create the linked server reference is executed on the Master database on our primary server rather than the HomeLending database. This is due to the fact that a linked server is a server object, rather than a database object, which can be available to any database that is created within the primary server.

Querying Linked Servers

In order to query the data contained on a linked server you must include the server name and instance name in the fully qualified name of each database object. If the OLE DB data source is a database other than SQL Server you will need to consider any syntax differentiation when writing queries against that data source. Listing 2 shows a sample of a query in which the credit report data is being combined with the loan application data in our sample database.

Listing 2. Query the Linked Server.

Network Security

There are some additional security considerations when implementing linked servers as a means of protecting sensitive information. Data will be traveling across network lines and potentially across the Internet. Performance will be affected but, more importantly given our security focus, it will also introduce a potential vulnerability to data being gleaned through hackers monitoring network traffic.

The encryption features of SQL Server are designed to protect data at rest, which is the data while it is in storage. Once the data is queried and decrypted it is considered data in transit and the protection of the data is dependent upon other security measures on the network such as Secure Sockets Layer (SSL). If the data that is stored on a linked server is encrypted, the encryption and decryption processes will likely occur at the linked server; otherwise the script in Listing 2 will need to include the commands for cryptographic functions.

Other -----------------
- Monitoring SQL Server 2005 Performance : Using Windows System Monitor & Using SQL Server Profiler
- Monitoring SQL Server 2005 Performance : Monitoring and Recording Performance
- SQL Server 2008 R2 : Replication - The Publisher, Distributor, and Subscriber Magazine Metaphor
- SQL Server 2008 R2 : Replication - What Is Replication?
- SQL Server 2008 High Availability : Other HA Techniques That Yield Great Results & High Availability from the Windows Server Family Side
- SQL Server 2008 High Availability : Building Solutions with One or More HA Options
- SQL Server 2008 High Availability : The Fundamentals of HA
- Administering SQL Server 2008 with PowerShell : Step-By-Step Examples (part 4)
- Administering SQL Server 2008 with PowerShell : Step-By-Step Examples (part 3) - Performing a Database Backup
- Administering SQL Server 2008 with PowerShell : Step-By-Step Examples (part 2) - Common OS-Related Tasks
- Administering SQL Server 2008 with PowerShell : Step-By-Step Examples (part 1) - General Tasks & Scheduling Scripts
- PowerShell in SQL Server 2008
- Protecting SQL Server Data : SCHEMA ARCHITECTURE STRATEGIES - Using Database Object Schemas
- Protecting SQL Server Data : SCHEMA ARCHITECTURE STRATEGIES - Protection via Normalization
- Troubleshooting and Optimizing SQL Server 2005 : Server Configuration Maintenance
- Troubleshooting and Optimizing SQL Server 2005 : Tuning the Database Structure
- Troubleshooting and Optimizing SQL Server 2005 : Data Analysis and Problem Diagnosis
- SQL Injection Attacks and Defense : Exploiting the Operating System - Consolidating Access
- SQL Injection Attacks and Defense : Executing Operating System Commands
- Administering SQL Server 2008 with PowerShell : PowerShell Scripting Basics (part 2)
 
 
Most View
- Programming WCF Services : Queued Services - Delivery Failures (part 1) - Configuring the Dead-Letter Queue
- .NET Components : Serialization and Class Hierarchies (part 2) - Manual Base-Class Serialization
- Parallel Programming with Microsoft Visual Studio 2010 : Introduction to Parallel Programming - Software Patterns
- SQL Server 2008: SQL Server Web Services - Building Web Services (part 1)
- Windows Server 2008 : Configuring FTP (part 14) - Using FTP Client Software
- SharePoint 2010 : Change Sorting and Filtering of a List or Library
- Developing an Information Architecture with Sharepoint 2010
- Managing Websites with IIS Manager (part 3) - The ASP.NET Account
- Windows Phone 7 Game Development : The World of 3D Graphics - The Depth Buffer
- Windows Home Server 2011 : Setting Up a Fax Server (part 2) - Sending a Fax
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS