How do I ensure Analysis Services client TCP connectivity is encrypted?
SQL Server Analysis Services often deals with sensitive financial numbers, sales figures, and strategic information used by businesses. Therefore it is natural to want to protect and encrypt data when is it queries to and fro from the Analysis Services over the TCP layers in Windows.
I’m am going to talk about network traffic encryption (incoming queries, results sent to clients like Management Studio and Excel, and writeback values sent from client to Analysis Services) and not storage encryption (ie. database files and hard drive) here. I’m ignoring storage encryption in this blog post since it is another beast, and unless you have physical access to the hard drive or backup tapes, it doesn’t matter as much in todays secure data centers. .
Option 1: Tell Windows to Encrypt everything in the TCP traffic on your internal network
You could require Windows to encrypt all network traffic on computers using windows features called IPSec and AuthIP. That’s outside my scope here, since that is complex to set up, and is usually a company wide effort as part of a security strategy, but its worth mentioning, since it means you encrypt traffic at a lower level and can secure systems more holistically.
External references:
Step-by-Step Guide to Internet Protocol Security (IPSec) https://technet.microsoft.com/en-us/library/bb742429.aspx
IPsec Configuration (Windows) https://msdn.microsoft.com/en-us/library/windows/desktop/bb736264(v=vs.85).aspx
Option 2: Encrypt in the Analysis Services TCP conversations
Or you could do encryption on a per-service basis.
Many DBAs are familiar with SQL Server Database Engine where you can tell the client provider like SQL Native Client (SQLNCLI) to force protocol encryption, and provide a signed certificate to use, either self-signed or by using a trustworthy certificate authority. Details here https://support.microsoft.com/kb/318605
The SQL Engine options are Force Protocol Encryption / ForceEncryption on the server, and connection string options Use Encryption for Data=True in OLEDB, Encrypt=YES in ODBC provider and SQL Native Client. Those methods use crypto APIs and either self-signed or 3rd party signed certificates (bring your own cert) to encrypt the TCP traffic.
Things are not the same for Analysis Services. Analysis Services and MSOLAP OLE DB provider used to connect to SQL Server Analysis Services does not have feature parity with SQL Native Client (used for SQL Engine connections) where it can do server encryption or client encryption in this fashion with a couple of connection string switches and registry keys to turn on ForceEncryption on the server side to tell it which certificate to use.
Analysis Services’ implements may be better or worse depending on your view point, but I’ll just say its different.
A. The client can override via connection string options to encrypt or not to encrypt, but its not common to do so.
Let’s go to Mosha’s blog here, which explains that Analysis Services traffic (XMLA over TCP) is by default compressed and encrypted.
As he explains these options can be turned on, or turned off if you want to sniff network traffic with Netmon or Wireshark and read the query and the results. So if your goal is to make data less secure for monitoring purposes, you could use these extra text connection string options, and you could see the plain text traffic (via network tracing) and decode which query is coming from the client.
Most secure client connection string options: Protection Level=pkt privacy;Transport Compression=Compressed;
Least secure (for monitoring traffic with a sniffer): Protection Level=none;Transport Compression=none; Protocol Format=XML;
To get this sniffing technique to work, I also had to allow anonymous logon (very dangerous). IN the msmdsrv.ini file I disabled authentication: <RequireClientAuthentication>0</RequireClientAuthentication>
Since I need to run a query without authentication as anonyous, I added a role and put anonymous into the role, and allow read permissions in the sample Adventureworks database. XMLA:
<Batch xmlns='https://schemas.microsoft.com/analysisservices/2003/engine' Transaction='true'> <Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="https://schemas.microsoft.com/analysisservices/2003/engine"> <Object> <DatabaseID>AdventureWorksDW2012Multidimensional-EE</DatabaseID> <RoleID>Role</RoleID> </Object> <ObjectDefinition> <Role xmlns:xsd="https://www.w3.org/2001/XMLSchema" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="https://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="https://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="https://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="https://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="https://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="https://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="https://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="https://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="https://schemas.microsoft.com/analysisservices/2012/engine/400/400"> <ID>Role</ID> <Name>Role</Name> <Members> <Member> <Name>NT AUTHORITY\ANONYMOUS LOGON</Name> </Member> </Members> </Role> </ObjectDefinition> </Alter> <Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="https://schemas.microsoft.com/analysisservices/2003/engine"> <Object> <DatabaseID>AdventureWorksDW2012Multidimensional-EE</DatabaseID> <DatabasePermissionID>DatabasePermission</DatabasePermissionID> </Object> <ObjectDefinition> <DatabasePermission xmlns:xsd="https://www.w3.org/2001/XMLSchema" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="https://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="https://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="https://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="https://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="https://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="https://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="https://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="https://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="https://schemas.microsoft.com/analysisservices/2012/engine/400/400"> <ID>DatabasePermission</ID> <Name>DatabasePermission</Name> <RoleID>Role</RoleID> <Process>true</Process> <ReadDefinition>Allowed</ReadDefinition> <Read>Allowed</Read> <Administer>true</Administer> </DatabasePermission> </ObjectDefinition> </Alter> </Batch>
For the brave of heart and curious, you can even use management studio to test this, or Excel. For Excel you could edit the .odc file and add the connection string switches.
That works for me. Let’s try the opposite approach:
You’ll be blocked by default though, as you’ll see why in Part B below.
B. The Analysis Server won’t let unencrypted client traffic in by default.
But the client can only choose to not encrypt if the Analysis Server lets it choose. By default the clients have little choice. You would have to adjust the Analysis Services server to remove the defaults and allow such unencrypted conversations or not via the msmdsrv.ini file settings.
By default, it does require all client connections to be encrypted for both administration and data, so there is really no need to touch these settings. The only reason to turn these options would be for some kind of traffic monitoring exercise or troubleshooting.
https://msdn.microsoft.com/en-us/library/ms175415.aspx
Security \ AdministrativeDataProtection \ RequiredProtectionLevel
The default setting of 1 requires that all administrative data be encrypted. A setting of 0 allows for clear text, and a setting of 2 allows for clear text with signatures (which is weaker than encryption).
Security \ DataProtection \ RequiredProtectionLevel
The default setting of 1 requires that all data be encrypted. A setting of 0 allows for clear text, and a setting of 2 allows for clear text with signatures (which is weaker than encryption).
Network \ Requests \ EnableCompression
EnableCompression is a Boolean server property that dictates whether data by default is compressed when it goes across the wire. This property appears twice in the General page of the Analysis Server Properties page. It appears once under Network \ Requests and once under Network \ Responses. The default value for requests is False because requests are relatively short. The default value for responses is True because responses are generally large amounts of data. To control whether requests and responses are streamed in binary format, see the EnableBinaryXML server property. You can also override this server behavior by specifying Transport Compression connection string property.
C. So by default the Analysis Services TCP traffic is encrypted by default but how is it done?
The good news as shared above is that is it always encrypted unless you loosen the restrictions via the server settings.
This begs the question, well how does AS do the encryption? So let’s assume you haven’t changed the connection string from the client, and the server defaults are in place… then how does it work?
The short story is that AS doesn’t do much of the work, but rather relies on Windows SSPI (Security Support Provider Interface) to do the heavy lifting of encryption on its behalf. This means depending on the SSPI authentication method chosen, NTLM or Kerberos protocols does the encrypting of the login packets and data buffers exchanged over TCP.
I did some monitoring of my system, and I see these core Windows SSPI dll’s do the work.
1. When I used SSPI=NTLM; in the MSOLAP connection string, it goes one route for the encryption…
msmdsrv.exe --> SSPICLI.dll --> msv1_0.dll
2. When I used SSPI=Kerberos; in the MSOLAP connection string, it goes an entirely different route for the encryption through many more layers of dlls. I confirm I get "Microsoft Kerberos V1.0" and I get KERB_ETYPE_AES256_CTS_HMAC_SHA1_96 on my Windows 8 testing, and I saw it load the cipher through some debugging magic (sorry I can’t share the internals).
Msmdsrv.exe --> SSPICLI.dll --> kerberos.dll --> Crypdll.dll ---> bcrypt.dll --> bcryptPrimitives.dll
So I could pick NTLM vs. Kerberos from the client connection string, to influence how the encryption is done. However, that doesn’t guarantee everyone in the company will remember to do that on a manual basis.
Is there any systematic approach to force one way or the other way for consistency and security? Stay tuned in part D.
D. Can I chose how the Analysis Services does the encryption?
In the most secure environments, you may want to be sure you use a top encryption cipher to be sure you know the traffic is encrypted safely to comply with your corporate standards, or government mandates (acronyms like FIPS, Sarbanes Oxley, and HIPPA come to mind)
1. Pick Kerberos or NTLM or both
If needed, you can restrict Analysis Services to use Kerberos authentication only, thus disabling NTLM (which encrypts differently), or visa versa.
To do so, edit the msmdsrv.ini file and replace the self closing tag <SecurityPackageList/> (which represented both NTLM or Kerberos are allowed) with this tag <SecurityPackageList>Kerberos</SecurityPackageList> or <SecurityPackageList>NTLM</SecurityPackageList>
Restart Analysis Services after the change to be sure the change is picked up and any open connections are cleared to enforce the new requirement.
In case you don’t know, the msmdsrv.ini file is a text file that lives in the default locations such as these, where the version and InstanceName may vary.
"C:\Program Files\Microsoft SQL Server\MSAS10.InstanceName\OLAP\Config\msmdsrv.ini"
"C:\Program Files\Microsoft SQL Server\MSAS10_50.InstanceName\OLAP\Config\msmdsrv.ini"
"C:\Program Files\Microsoft SQL Server\MSAS11.InstanceName\OLAP\Config\msmdsrv.ini"
2. An aside: Don’t forget the SPNs for Kerberos
If you decide to opt for Kerberos only, in order for double hop to work, you need to be sure to create the MSOlapSvc.3 SPN in the Active Directory on the domain service account (or the computer object depending on the service account) using the SetSPN.exe tool, so that Kerberos will work, and double hop will work as needed. Full instructions here https://support.microsoft.com/kb/917409
NTLM cannot do double hop, so it is a more limited way to do authentication, and the SPN is not needed for NTLM to work.
3. If needed, force a Cipher and Strength for NTLM or Kerberos
Caveat 1: I don’t have many versions of windows to test with. I think this options work easily in newer versions of Windows such as Win 7, Win 2008 R2, Win 8 only.
Caveat 2: Also, if you set these things at a group policy, you can impact (and potentially break) a whole bunch of computers, so proceed with caution and test test test in a lab environment first!!!!
There are both group security policy and local security policy settings for both NTLM and Kerberos to restrict which cipher and cipher strength is used for the encryption used by NTLM and Kerberos.
Check it out in your Administrative tools from the start menu.
Start > Run > gpedit.msc (or use WIn key + R)
Local Computer Policy
> Computer Configuration
> Windows Settings
> Security Settings
> Local Policies
> Security Options
The three relevant settings are highlighted above, and color coded for illustration:
Network security: Configure encryption types allowed for Kerberos
Network security: Minimum session security for NTLM SSP based (including secure RPC) clients
Network security: Minimum session security for NTLM SSP based (including secure RPC) servers
So to pick the cipher, you can do so on each individual computer via the Local Security Policy snap in shown above, or you can deploy the encryption encryption restrictions to many computers using a Group Security Policy across an organization unit of multiple computers in the company . As I said before, be careful with group policies, since you may break something.
To roll out any group policy with these Kerberos or NTLM restrictions, you could put the computers which need to be encrypted into the Organizational Unit (OU) with this policy defined to ensure the policy will take effect on those clients. Be aware that restricting the cipher may affect other applications outside of Excel and Analysis Services which also rely upon Kerberos authentication, so be sure to test line of business applications thoroughly with this configuration.
In the above settings, the detailed property dialogue lists each cipher is listed with a checkbox, to enable each cipher individually according to your security requirements. Such as AES256_HMAC_SHA1 as shown here for Kerberos: Network security: Configure encryption types allowed for Kerberos
And for NTLM a much shorter list of ciphers:
Network security: Minimum session security for NTLM SSP based (including secure RPC) clients
Network security: Minimum session security for NTLM SSP based (including secure RPC) servers
Note: Different versions of Windows and functional different levels of domain controllers will offer different options, so I can’t exhaustively tell if you this will work on your environment or not. Some options are new to Win 7 and 2008 R2 releases too.
NTLM https://technet.microsoft.com/en-us/library/dd566199(v=ws.10).aspx
Kerberos: https://technet.microsoft.com/en-us/library/dd560670(v=WS.10).aspx
A good reference about the newer options: https://blogs.msdn.com/b/openspecification/archive/2011/05/31/windows-configurations-for-kerberos-supported-encryption-type.aspx
Another Caveat:
Because I am not intimately familiar with all the internals of how NTLM and Kerberos works, the Explain tab is helpful to clarify, so I’ll paste references to read more. I don’t have the answers to exactly how these settings work, or which versions of windows can do each kind of encryption, so I’ll leave it to you to test and research the specifics.
- Network security: Configure encryption types allowed for Kerberos https://blogs.msdn.com/b/openspecification/archive/2011/05/31/windows-configurations-for-kerberos-supported-encryption-type.aspx
- Network security: Minimum session security for NTLM SSP based (including secure RPC) clients https://technet.microsoft.com/en-us/library/cc736506(v=ws.10).aspx
- Network security: Minimum session security for NTLM SSP based (including secure RPC) servers https://technet.microsoft.com/en-us/library/cc776157(v=WS.10).aspx
Option 3: Use HTTPS with the msmdpump.dll Data Pump to use a custom certificate
You can have clients like Excel, Management Studio, Report Servers, and custom apps which use MSOLAP provider to connect to an HTTP or HTTPS address to talk to Analysis Services and run queries.
They could remotely access Analysis Services across the WAN/internet (scary for security purposes) or just on your local intranet behind a safe firewall.
This kind of set up is done with an ISAPI extension called the data pump. The msmdpump.dll is a DLL which ships in the Program Files folders and can be put into IIS to do the HTTP(S) connectivity work, and converting the traffic to TCP connectivity to Analysis Services. This means you can use HTTP traffic over port 80 or a port of your choosing, and leave the Analysis Services ports like 2383 (default instance) port 2382 (SQL Browser for AS) or dynamic ports (used in AS named instances) closed in the firewalls. If you secure the conversation (as I suggest you do) as HTTPS, the default port becomes TCP 443, which is usually already open on most firewalls.
When configured, the msmdpump acts as a middle man living in your IIS server, to forward the HTTP(S) queries (port 80 or port 443 traffic) and result sets to a fro the Analysis Services (port 2383 or dynamic port traffic). The data pump uses the same MSOLAP OLEDB provider (the copy installed locally on the IIS machine) to talk to the Analysis Services on your behalf. It has a msmdpump.ini configuration file to point to the right instance of Analysis Services (hostname for default, or hostname\instance name). You can have IIS on a dedicated server or on the same machine as Analysis Services depending on your hardware and virtualization VM options. Authentication of the client application can be done to impersonate the caller (an IIS setting to allow Digest or Basic security) or to allow anonymous (anyone) to have access. Impersonation is nice, since the calling application can connect using its Windows credentials and see the security as if it was connected directly to the Analysis Services.
To securely encrypt the HTTP conversations, just add the S – HTTPS. That means you’ll need a certificate that IIS can use to secure the URL and encrypt the traffic. Its best to use a certificate authority (there are several 3rd party like VeriSign, or even build your own Microsoft certificate authority) to get the certificate, so it can be independently verified each time a new request comes in to be sure it is a secure connection. The complexity is to enable the IIS configuration for SSL enabled, with the optional Trusted Certificate on client side, and setting "Require Client Certificate" on IIS.
The details of a variety of IIS versions and SSL options are complex, but here are some references.
Instructions on Win 2003 https://technet.microsoft.com/en-us/library/cc917711.aspx
Instructions on Win XP https://technet.microsoft.com/en-US/library/cc917712.aspx
Instructions on IIS 7 https://msdn.microsoft.com/en-us/library/gg492140.aspx
Steps of Configuring Analysis Service Data Pump on IIS 7.docx
I see there are numerous forum postings discussing failure with HTTPS and Excel, so it is a bit temperamental, so plan some time to test and be sure to try the various different client applications if you opt for msmdpump over HTTPS.