Category Archives: SQL

Managed Service Accounts in Server 2012 R2

Posted on by

Managed Service Accounts were first introduced in Server 2008 R2. They are a clever way to ensure lifecycle management of user principals of windows services in a domain environment. Passwords for these accounts are maintained in Active Directory and updated automatically. Additionally, they simplify SPN management for the services leveraging these accounts. In Server 2012 and above, these can also be configured as Group Managed Service Accounts which are useful for server farms. A common scenario for using a managed service account may be to run a the SQL Server service in SQL 2012.

There are a few steps involved in creating these managed service accounts on Server 2012 R2. First, there is a dependency on the Key Distribution Service starting with Server 2012 (in order to support group managed service accounts, though it’s now required for all managed service accounts). You must configure a KDS Root Key. In a production environment, you must wait 10 hours for replication to complete after creating the key, but in lab scenarios with single domain controllers, you can force it to take effect immediately:

Add-KdsRootKey -EffectiveTime ((get-date).addhours(-10))

Once the key has been created, you can create a managed service account from a domain controller. You will need to import the AD Powershell module. We’ll create a MSA named SQL01MSSQL in the contoso.int domain for use on a server named SQL01

Import-Module ActiveDirectory

New-ADServiceAccount -Name SQL01MSSQL -Enable $true -DNSHostName SQL01MSSQL.contoso.int

Next, you’ll need to specify which computers have access to the managed service account.

Set-ADServiceAccount -Identity SQL01MSSQL -PrincipalsAllowedToRetrieveManagedPassword SQL01$

Lastly, the account needs to be installed on the computer accessing the MSA. You’ll need to do this as a domain admin and the AD Powershell module installed and loaded there as well:

Enable-WindowsOptionalFeature -FeatureName ActiveDirectory-Powershell -Online -All

Import-Module ActiveDirectory

Install-ADServiceAccount SQL01MSSQL

You can now use the MSA in the format of DOMAINNAME\ACCOUNTNAME$ with a blank password when configuring a service.

 

Using same remote SQL 2012 SP1 instance for DPM 2012 SP1 and DPM 2012 R2

Posted on by

We recently began to deploy DPM 2012 R2 into our environment. For ease of management, we use a single remote SQL instance for all of our DPM installations. Naturally, we decided to use the same remote SQL 2012 SP1 instance for new DPM 2012 R2 installs.

One of the first steps requires that you run the DPM Remote SQL Prep on the SQL server. When we ran this from the DPM 2012 R2 installation media, it upgraded the existing DPM 2012 SP1 Remote SQL Prep files causing all of the existing jobs on the DPM 2012 SP1 servers to fail. The errors were not evident in the DPM console, rather they were logged to in the SQL Agent on the remote SQL instance:

Message
Executed as user: DOMAIN\sqlservice. The process could not be created for step 1 of job 0x8ADCFE6FE202F04F8C7A11C240E42059 (reason: The system cannot find the file specified). The step failed.

The resolution was to re-run the DPM Remote SQL Prep install from the DPM 2012 SP1 media AFTER the DPM Remote SQL Prep install was run from the DPM 2012 R2 media on the remote SQL server. This restored the necessary files on disk and jobs began running again immediately.

Resolving error 0x8007007e Cannot cannect to wmi provider

Posted on by

Recently, I had to troubleshoot a problem with SQL backups via Microsoft Data Protection Manager 2012 SP1 for a SQL Server 2008 system. DPM was alerting us that database auto-protection failed with error code ID 32511. The detailed errors showed that DPM could not enumerate SQL Server instances using Windows Management Instrumentation on the protected computer. This error was detailed in the DPMRACurr.errlog on the production server:

WARNING Failed: Hr: = [0x8007007e] : unable to execute the WQL query: SELECT * FROM ServerSettings

This pointed to a problem with the underlying WMI configuration for SQL, so I used wbemtest.exe from the remote DPM server to test WMI connectivity. If you are unsure of exactly what WMI namespaces are in use or what queries are being run, you can use WMI Tracing to see what’s happening under the hood.

Log Name: Microsoft-Windows-WMI-Activity/Trace
Source: Microsoft-Windows-WMI-Activity
Date: 10/22/2013 3:59:39 PM
Event ID: 1
Task Category: None
Level: Information
Keywords:
User: SYSTEM
Computer: SERVERNAME
Description:
GroupOperationId = 9283379; OperationId = 9300341; Operation = Start IWbemServices::ExecQuery – SELECT * FROM ServerSettings; ClientMachine = DPMSERVER; User = jeff; ClientProcessId = 2540; NamespaceName = \\.\root\Microsoft\SqlServer\ComputerManagement10

Once wbemtest is open, connect to the appropriate namespace:

SQL 2005
\\SERVERNAME\root\Microsoft\SqlServer\ComputerManagement

SQL 2008 & 2008 R2
\\SERVERNAME\root\Microsoft\SqlServer\ComputerManagement10

SQL 2012
\\SERVERNAME\root\Microsoft\SqlServer\ComputerManagement11

Once connected, try executing the WQL query that your application is using – in my case, it was SELECT * FROM ServerSettings. Doing this resulted in the error:

Number: 0x8007007e
Facility: Win32
Description: The specified module could not be found.

Some quick research shows this can most often be resolved by recompiling the WMI template for SQL with mofcomp:

http://support.microsoft.com/kb/956013

On 64-bit Windows with SQL 2008, the command is:

mofcomp “C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof”

You may need to adjust the command for bitness and version of SQL and then restart the WMI service for the changes to take effect. However, this did not resolve the issue on the specific system where I was encountering the problem. The same error was returned when trying to run a query in wbemtest after recompiling and restarting the service, the DPM console also displayed the same error when attempting to enumerate SQL instances. The 0x8007007e error typically means a DLL or registration is missing. Time to break out procmon and see what’s happening under the covers. Using filters to include only the wmiprvse.exe process and excluding entries with a SUCCESS result, I could see that there was a file it seemed to be looking for, but could not find:

Procmon WMI SQL

 

It seemed to be scouring the path looking for sqlmgmprovider.dll and svrenumapi100.dll. I checked on disk, and sure enough, neither of those files existed under the path C:\Program Files\Microsoft SQL Server\100\Shared, however, their 32-bit counterparts were located under C:\Program Files\Microsoft SQL Server\100\Shared. Checking another  64-bit SQL 2008 server, I was able to find those files under that first path. After copying them from a known working system, the error was resolved. Also, the second file was only listed in procmon once I copied the first to the server and retested, so it make take several passes to completely resolve.

Note that this resolved this specific error for me, though it may not be the best solution. The reason those files were not on the server is because there was only a 32-bit instance of SQL Server on the system. By adding those two files and re-running wbemtest, an error was no longer returned, but the query also did not show any instances of SQL Server because it was querying for 64-bit instances.

Using SQL Database Audits for change detection

Posted on by

There are several reasons you may want to audit the data in your SQL databases. It’s helpful when trying to track down a bug in software, trying to piece together the source of unexpected data, or to meet compliance doctrines. Depending upon your needs, there are several ways to accomplish this. If you need auditing in order to be able to track and revert changes to data, trigger-based table auditing is probably best.

However, when you need to generate audit trails for compliance reasons, Microsoft’s SQL Server has a few built-in ways of doing so. C2 auditing is supported, however the amount of data generated makes this a non-viable option for most installations. Starting with SQL 2008, Microsoft introduced a new high-performance feature called SQL Server Audit. This functions at both the server level (included with all editions) and the database level (enterprise edition). This will allow you to audit a myriad of server and database level functions, and can help ensure your SQL footprint is in compliance.

SQL Server audit is composed of a few different functions. At the server level, you can define an auditing configuration that writes to a file, the NT Application Log, or the NT Security Log. Logging to a file has the least performance overhead and writing to the Security log requires special permissions. From there, you can configure Server level auditing, and/or Database level auditing. The auditing rules can be fine-tuned to apply to specific objects in the database and for specific user principals. For this example, I will configure auditing of Update and Delete events for all tables in a database for all users – a scenario that can effectively meet a requirement commonly known as File Integrity Monitoring or Change Detection for audit trails.

First, we’ll setup an auditing specification to log events the Application Log. Under Security, right-click Audits and select New Audit. Give the Audit an appropriate name and select Application Log as the destination. Click OK. Then right-click the Audit that is created and select enable.

Next, go to the database you would like to audit. Under Security right-click Database Audit Specification and select New Database Audit Specification. Name the database audit and select the appropriate audit configuration (AppLog). Under Actions, select the actions you would like to audit, one at a time. Set the Object Class to Database, select the database in the Object Name column, and select the [public] database role in the Principal Name column to audit all users. Even though the pop-up window will allow you to select multiple items, each row can contain only 1 object and principal. Click OK. Then right-click the Database Audit that is created and select enable.

Once enabled, you can now test your audit configuration by updating or deleting database for any table in the audited database. Right-click on the audit specification at the server level and select View Audit Logs to see generated audit events.

 

Backup Database using MySQL Workbench

Posted on by

It’s possible to backup a MySQL database remotely even if you do not have administrative privileges. Most articles describe running mysqldump directly on the server, but that’s not always possible. Fortunately, you can do this from MySQL Workbench. I was able to backup the WordPress database from my Cytanium Shared Windows Hosting account.

First, you’ll want to download and install the latest version of MySQL Workbench (I used 5.2.39). After installing, you’ll need to configure a Server Administration connection:

Follow the instructions in the wizard by entering the remote host address, username, password and default schema. If your account does not have root privileges, you will want to select “Do not use remote management.” Once complete, double-click on the new connection listed under Server Administration. Under Data Export / Restore, select Data Export:

Select your database, export to a self-contained file, and dump stored routines. Click Start Export when ready. MySQL WorkBench will then export the database schema and data to a .sql file you can use to restore your database.

SQL Server Authentication Channnel Encryption

Posted on by

We had a customer recently inquire as to whether the authentication channel between a client and a SQL Server was encrypted by default. While we know that SSL is supported on SQL Server 2005, we did not have a certificate installed. However, it was rumored that the system would use a self-signed certificate. Also, we wanted to explore the differences between SQL Server 2000 and SQL Server 2005, as well as the differences between different providers.

I searched for documentation confirming that the authentication channel was indeed encrypted and was able to come up with the following from this MSDN article: http://msdn2.microsoft.com/en-us/library/ms189067.aspx

“Microsoft SQL Server 2005 can use Secure Sockets Layer (SSL) to encrypt data that is transmitted across a network between an instance of SQL Server and a client application. The SSL encryption is performed within the protocol layer and is available to all SQL Server clients except DB Library and MDAC 2.53 clients.”

“Credentials (in the login packet) that are transmitted when a client application connects to SQL Server 2005 are always encrypted. SQL Server will use a certificate from a trusted certification authority if available. If a trusted certificate is not installed, SQL Server will generate a self-signed certificate when the instance is started, and use the self-signed certificate to encrypt the credentials. This self-signed certificate helps increase security but it does not provide protection against identity spoofing by the server. If the self-signed certificate is used, and the value of the ForceEncryption option is set to Yes, all data transmitted across a network between SQL Server and the client application will be encrypted using the self-signed certificate.”

Well, this contradicted some posts I had read but did point us in the right direction. So, we decided to test this. I setup two Virtual Machines, one running a .NET web application and a .NET windows application, and one running SQL Server 2005 Express Edition. I then installed Network Monitor 3.1 and captured the traffic on the NIC as we tested the connection using the SQL Native Client (SQLNCLI) and OLEDB (System.Data.SqlClient).

We saw the server send a self-signed certificate to the client and after which, the authentication channel was encrypted. We also ran the same test on SQL Server 2000. While the authentication channel is not encrypted with SQL Server 2000, the password is not sent in clear text. Rather, it appears obfuscated – most likely using an offset of some kind. We did see the username come across in clear text.

To summarize, when using SQL Server 2005, the authentication channel is completely encrypted when using any clients except DB Library and MDAC 2.53 clients regardless of whether the server has a SSL certificate installed.