Ralf Eisenreich

In the following you find a compact catalog of steps which should be considered in a SQL Server environment.
It is a conclusion of information from different sources.

Check List
Installation & Patching

• Are only required components installed? Additional components should be installed when required.
• As Service Account a Local or Domain Account should be rather used than a System Account.
• A separate account for each service should be used.
• Service accounts should use standard privileges (no special). The privileges are applied per Group Membership (SQL Server supplied group accounts).
• Service Accounts should be applied with SQL Server Configuration Manager.
• Always the most recent updates should be used.
• Do not apply automatic updates. Rather test them before on a test environment.

Configuration: Surface Area Reduction

• Surface Area Configuration Tool: only required features should be enabled.
• Only Required Services should be set to Auto startup. Others should be set to Manual startup. Also consider to set some Services to Disabled startup.
• If not needed supported Network protocols should be disabled in Configuration Manager.
• If not needed, SQL Server should not be exposed to all networks (e.g. the Internet).
• For some Configurations it is easier if named instances use fixed ports, rather than dynamic ports.
• Surface Area Configuration Tool: the xp_cmdshell should be disabled unless it is absolutely needed.
• Surface Area Configuration Tool: COM components should be disabled once all COM components have been converted to SQLCLR.
• Surface Area Configuration Tool: both mail procedures (database mail and SQL mail) should be disabled unless it required to send mails from SQL Server. Rather use database mail.
• Surface Area Configuration Tool: standard policy should be enforced for extended procedure usage.
• No system stored procedure should be removed by dropping it.
• User or Administrator access should not be denied to the extended procedures by using DENY all.
• Surface Area Configuration Tool: All exceptions to the above statements should be documented here.

SQL Server Agent

• In order to apply special privileges to Jobs, Credentials should be used, instead of adjusting the privileges of SQL Server Agent service account.
• In order to execute a Job with different Windows credentials, Proxy accounts should be used.

Security: Authentication (general)

• Always Windows Authentication should be used. Mixed Authentication should be used only for Non-Windows accounts or incompatible applications.
• The sa account should have a strong and known password.
• If possible, the sa account should be renamed.
• The SQL Server Management should not be done using the sa account. Rather use a known login and assign the sysadmin privilege to it.

Security: Authentication (administrative access)

• Use administrative privileges only when needed to access SQL Server.
• The number of administrators should be as low as possible.
• Administrative logins should not be linked to the BuiltIn\Administrators group.

Security: Authorisation

• Permissions on DB objects should be managed using Database roles or Windows groups.
• Guest access should not be enabled.

Security: Scanner Tools

• Use Microsoft Best Practices Analyser to identiy possible weak points in SQL Server 2005.
• Use Microsoft Baseline Security Analyser to identify weak points in your operating System.

Security: Password Policy

• Set up a strong password policy, including an expiration and a complexity policy. E.g. use the company password policy.
• If using SQL logins, SQL Server should run on Windows Server 2003 OS. Also password policies should be mandatory.
• Application should support the change procedure for SQL Login passwords.
• New SQL Logins should have the property MUST_CHANGE_ON_FIRST_ACCESS on true.
• Security on Database objects should be managed by using schemas.

Database Properties

• Databases should have distinct owners. The sa user should not own all databases.
• The number of owners per Database should be as low as possible.

Schema Properties

• Similar SQL objects should be grouped into the same schema.
• Schemas should not be owned only by dbo.
• The number of schema owners should be minimized.

Catalog Views

• Catalog views are secure by default, so no additional action is required to secure them.

Remote Data Source Execution

• Instead of Remote server definitions rather Linked Servers should be used.
• Ad hoc queries through Linked Servers should be disabled if not needed.

Execution Context

• Rather use EXECUTE AS instead of SETUSER.
• Rather use WITH NO REVERT/COOKIE instead of Application Roles.

Data Encryption

• Information which is classified as secret (or confidential) should be encrypted.
• Data encryption should be done with symmetric keys which are protected by using asymmetric keys or certificates.
• Keys should be password-protected and the master key encryption should be removed for the most secure configuration.
• The service master key, database master keys, and certificates should be backed-up by using the key-specific DDL statements.

Auditing

• The amount of auditing data (detail level) should be project specific.
• C2 auditing should be enabled only if explicitly needed.
• DDL and specific server events should be audited by using trace events or event notifications.
• DML must be audited by using trace events.
• WMI should ber used to be alerted of emergency events.

Fazit
My recommendation is to use following Test Protocol in order to check the several steps.

Download: SQL Server: Check List Best Practices

Wireshark Logo

Wireshark is the successor application of Ethereal. Wireshark is a free (GNU General Public License) network traffic analyser (packet sniffer).
This tool is very useful to test encrypted connections of own applications or to analyse active connections and data flows on the own desktop.

Here is an application screenshot:
The application layout consists of 3 parts (windows) – packet list, packet details and packet bytes.

Wireshark Application Screenshot

A sample ICQ data flow:
This data flow contains the message: “Gratulation zum Motoradführerschein “

ICQ Data Flow

Supported Systems:
Wireshark runs on Unix-like systems (e.g. Linux, Solaris, HP-UX, FreeBSD, NetBSD, OpenBSD and Mac OS X) and on Microsoft Windows.

[Source: Wireshark]

Linked SQL Server (applies to SQL Server 2005 / SQL Server 2008 / later)
It can happen that an application may need data stored in second database which is not located on the same instance of SQL Server. That instance could be on the same physical machine or it could be on another machine. There are many solutions to this scenario, but it depends on your requirements which solution you should choose. If there is no need to transport a huge amount of data between the two SQL Server instances then you should consider to register the second instance as Linked SQL Server into the first instance.

The advantage is that the Linked Server can provide seamlessly data access to the application. By the way, Linked Servers can be also used to register Non-SQL-Server instances.

Basic Architecture Example
The following illustration shows a basic architecture sample. As you can see the Client is only accessing the Application Server and the Application Server is only accessing the first SQL Server.

Linked SQL Server: Basic Architecture

This means the Application Server is submitting only one query to the first SQL Server instance and gets the result back only from the the same SQL Server instance. The data retrieval from the second SQL Server instance is processed by the first SQL Server instance.
More detailed: If the application running on the Application Server submits a query to the frist SQL Server instance that needs to retrieve data from a database hosted on the second SQL Server instance, then a so called Distributed Query is fired. An Example for such a query:


SELECT SERVER2.[DB].[SCHEMA].[TABLE].[FIELD]
FROM SERVER2.[DB]


In this sample the SERVER2 (= Linked Server Registration name on SERVER1) identifies that I am starting a distributed query.

How to setup the Linked Server
You can register a Linked Server instance within Management Studio as the following illustration shows.

Linked SQL Server: Management Studio

For a greater flexibility I recommend to use the stored procedure.

EXEC master.dbo.sp_addlinkedserver
@server = N'REGISTRATION_NAME',
@srvproduct=N'',
@provider=N'SQLNCLI',
@datasrc=N'SERVER\INSTANCE',
@provstr=N'',
@catalog = 'DATABASE_NAME'


Linked Server Options: Server Options
Following Server options are available:

• Collation Compatible: is used to identify whether or not the Linked Server has the same collation as the Local Server, should be set only the to true if you know for sure that both instances have the same collation.
• Data Access: is used allow data access on the linked server, should be true. This option can be used to disable a Linked Server temporally.
• RPC: is used to allow remote procedures calls from the Linked Server.
• RPC out: is used to allow remote procedures calls to the Linked Server (procedures which are defined on the second instance).
• Use Remote Collation: is used to specify that the collation setting from the remote instance is used.
• Collation Name: is to specify the collation setting of the Linked Server.
• Connection Timeout: is used to specify a time the Local Server waits to obtain a connection to the Linked Server (Zero used the remote instance setting).
• Query Timeout: is used to specify a time the Local Server waits to retrieve data from the Linked Server (Zero used the remote instance setting).

Linked Server Options: Security

• Login mapping: is a way to associate a login on the Local Server, with a login on the Remote Server. There are two options – Impersonate login and SQL login.
• Impersonate login: uses a local Windows login and uses it to connect to the Linked Server, by impersonating it. For this login delegation has to be enabled between the two Servers!
• SQL login: is used by associating a local login with a remote login and password. The remote login needs to be a SQL Server Authenticated user on the remote server.

For logins which are not defined in the mapping you can specify the behavior for the connection to the linked server. There are four different options that are available by choosing the corresponding radio button.

• Not be made: any users who are not added to the mapping list cannot connect to the Linked Server.
• Be made without using a security context: can be used for Data Sources which do not require any authentication. E.g. text files.
• Be made using Login’s current security context: the Windows account of the current login is used to connect to the Linked Server. The Local Server has to be able to impersonate the corresponding local account. This option is a simple way to specify that all Windows accounts are able to connect to the Linked Server, without mapping each login.
• Be made with this security context: specifies that all logins connecting to the Linked Server are using a single remote login and password (SQL Server Authenticated login).

Encryption of Connection stream
SQL Server is able to encrypt the connection between Client and Server. Since SQL Server 2005 you do not necessarily need to use SSL certificates. However, it makes sense to have certificates, because the Client can not check the identity of the Server. By the way, if you use a certificate which was generated from the Server itself then it will not be trustful.

The Login packets are always encrypted since SQL Server 2005, if the Client supports it. Encrypted communication between Client and Server is supported since MDAC 2.6 and third party Client tools should be ckecked. It is important to know that encryption takes additional CPU time and that encrypted data streams cannot be compressed anymore.

The encryption option can be activated in the Configuration Manager (SQL Server Network Cobnfiguration). Choosing the Properties dialog for an Instance you can set the option “Force Encryption” to true.
With this option set to true the SQL Server instance only accepts encrypted connections. If you want to have both connection options (encrypted / non-encrypted) then you should not set this option to true.

If you do not choose a certificate (under certificate tab) then SQL Server 2005 (or later) generates a self-signed certificate. This means that the communication between Client and Server is encrypted and that spoofing of data is not possible anymore.

Encryption of Linked Server Connection Stream
To enable the encryption of a Linked Server connection stream you should use the provider string option “Encrypted=YES”. The following stored procedure includes this option.


EXEC master.dbo.sp_addlinkedserver
@server = N'REGISTRATION_NAME',
@srvproduct=N'',
@provider=N'SQLNCLI',
@datasrc=N'SERVER\INSTANCE',
@provstr=N'Encrypt=yes;',
@catalog = 'DATABASE_NAME'

The Linked SQL Server connection is now encrypted using a self-signed certificate or if available a Server certificate.

How to check encrypted connection for SQL Server
I recommend the tool Wireshark (former known as Ethereal) to check the packages and look into the data. Just run this tool, capture a stream and fire a SELECT statement.

Conclusion
Registering another SQL Server instance as Linked Server allows you to submit T-SQL statements on one SQL Server instance, which retrieves data from a second instance. Moreover it is possible to fire linked statements so that you can join data between several instances.

Falls im Sicherheitscenter weder Windows Defender noch Antivirus-Software erkannt wird, hilft folgendes:

1. Start – Ausführen – “services.msc” starten und Dienst Windows-Verwaltungsinstrumentation stoppen
2. unter C:\Windows\system32\wbem\repository den Ordner löschen oder umbenennen
3. den WMI-Dienst wieder starten

Das sollte helfen.

Folgende Motivationen können es veranlassen “sicher” zu surfen:

• auf einem Shared PC sollen wichtige Daten abgerufen werden (z.B. Online Banking)
• der Internet-Provider blockt einige Inhalte im Internet (gängige Praxis in China, UAE oder Saudi Arabien)
• es soll nicht ohne weiteres nachvollziehbar sein (z.B. beim Arbeitgeber), welche Inhalte abgerufen worden sind

Lösung:
Die einfachste Lösung ist das Surfen über einen abgesicherten Proxy, dem man selbst vertraut. Hier möchte ich die Möglichkeit vorstellen, wie unter Windows ein SSH-Tunnel eingerichtet werden kann.

Vorraussetzungen

• PuTTY auf dem lokalen Rechner
• einen Remote Host, auf dem OpenSSH läuft (beispielsweise ein Linux-Rechner zu Hause)

(more…)