Having spent 6 years of my life working in internal IT for a publicly traded insurance company here in the United States, I am fully aware of the legal requirements and restrictions that are enforced on corporate IT infrastructures. It seems like every day we hear news stories about data breaches, hacks and the like, so IT managers and IT Security professionals are rightfully vigilant to protect their environment and keep the company name out of the news spotlight. Despite the honorable goal of ensuring security, some organizations can be overzealous and go a bit too far in their quest of uber-security, which can have real-world ramifications to Lync Server and Skype4B Server deployments. While I am not advocating a complete disregard for the attempts at security hardening, I do question the scope that some companies choose to implement those security changes and respectfully challenge them to use some common-sense approaches in the balance between true security, usability and marking off a check box as a “compliance requirement”.
The Dizzying Array of Compliance Requirements
While not a complete list, a small smattering of compliance requirements that exist today:
Global
United States
European Union
Best Practice Guides
SQL Server 2008 R2 Security Best Practice Whitepaper
SQL Server 2012 Security Best Practice Whitepaper
SQL Server 2014 Security Considerations
SQL Server 2016 Security Considerations
And that’s just a small list. The number of requirements and recommendations are increasing every year and each new requirement leaves fewer stones un-turned. Red-tape, indeed… The never-ending challenge becomes meeting the regulatory requirements and/or best practices while not resulting in unplanned operational issues or undue administrative burden. The SQL issues discussed below have all surfaced due to changes enacted within customer environments as a direct result of some of the guidance and/or requirements above. I’ll update the article to include additional issues as I come across them.
Renaming the SQL ‘sa’ Account
This particular best practice comes right out of Microsoft’s own documentation, specifically the SQL Server Best Practice Whitepapers listed above:
Best practices for authentication mode and logins
• Always use Windows Authentication mode if possible.
• Use Mixed Mode Authentication only for legacy applications, non-Windows users, and users from untrusted domains.
• Use the standard login DDL statements instead of the compatibility system procedures.
• If the sa account is not going to be used, you should disable it. Change the sa account password to a known value if you might ever need to use it. Always use a strong password for the sa account and change the sa account password periodically.
• Do not manage SQL Server by using the sa login account; assign sysadmin privilege to a knows user or group.
• Rename the sa account to a different account name to prevent attacks on the sa account by name.
• Do not delete internal built-in logins
• Use Windows Logins rather than Windows Group to control access to SQL Server and use care when using Windows Group logins to prevent group overlap for a particular user.
• Use login triggers for more granular control of the login process.
While such a simple and seemingly innocuous recommendation, it actually results in a nasty issue with deploying Lync Server and/or Skype for Business Server databases:
Error: Set owner failed for Database 'xds' Error: The login 'sa' does not exist on this server.
Examining the SQL log file shows some additional information:
Executing Xds.sql...
...
Setting owner for database xds to sa.
Exception Stack:
Type: Microsoft.SqlServer.Management.Smo.FailedOperationException, Message: Set owner failed for Database 'xds'.
Type: Microsoft.SqlServer.Management.Smo.SmoException, Message: An exception occurred in SMO.
Type: System.ArgumentException, Message: The login 'sa' does not exist on this server.
Examining the T-SQL scripts proved fruitless as well, as it does not appear that the scripts hard-code the name ‘sa’ to any owner parameters.
The Fix?
This particular issue is actually already documented on TechNet. The particular customer I was working with at the time had renamed their ‘sa’ account to ‘sql_sa’. There is no real fix for this issue other than re-naming the built-in account back to ‘sa’ and running through the database installation again.
From a practical perspective, renaming the ‘sa’ account sounds security conscious but it is truly security through obscurity because you are only changing the name and not the underlying SID of the account (which remains the same). Additionally, if you have the ‘sa’ account disabled, which you should, changing the name gets you no additional safeguards as you can’t log in with an account that is already disabled, regardless of the name of that account. This requirement likely comes from legacy thinking where some apps or administrators may use the built-in ‘sa’ account for application or operational access so changing the name keeps people from attempting to log in using default names. On the other hand, having the ‘sa’ account enabled allows a final safeguard in getting into a SQL instance should things go haywire, so there is a small scenario where having the account enabled (and potentially re-named) may be useful. My personal advice: just disable the ‘sa’ account and leave things as is.
Get-CsDatabaseMirrorState Returns ‘DatabaseInaccessibleorMirroringNotEnabled’
This particular issue could be the result of SQL mirror problems, but it is more likely a result of the user account running the cmdlet not having sysadmin role rights within the SQL instances used to store the back end databases. When you don’t have sysadmin rights, the cmdlet returns information for the StateOnPrimary value but returns incomplete information for the StateOnMirror value:
StateOnMirror: DatabaseInaccessibleOrMirroringNotEnabled
When you run SQL Tracing on SQL Server during the cmdlet execution, you see the Get-CsDatabaseMirrorState cmdlet executes the following T-SQL commands:
select db.name, m.mirroring_role_desc, m.mirroring_state_desc from sys.database_mirroring as m join sys.databases as db on db.database_id = m.database_id where db.name in ('rtcab','rtcxds','rtcshared')
select db.name, m.mirroring_role_desc, m.mirroring_state_desc from sys.database_mirroring as m join sys.databases as db on db.database_id = m.database_id where db.name in ('lcscdr','qoemetrics')
select db.name, m.mirroring_role_desc, m.mirroring_state_desc from sys.database_mirroring as m join sys.databases as db on db.database_id = m.database_id where db.name in ('lcslog')
select db.name, m.mirroring_role_desc, m.mirroring_state_desc from sys.database_mirroring as m join sys.databases as db on db.database_id = m.database_id where db.name in ('rgsconfig','rgsdyn','cpsdyn')
select db.name, m.mirroring_role_desc, m.mirroring_state_desc from sys.database_mirroring as m join sys.databases as db on db.database_id = m.database_id where db.name in ('xds','lis')
Executing those T-SQL commands manually within SQL Management Studio always returns empty information for the SQL server that is currently running as the mirror:
SQL PrimarySQL Mirror
You can see above that the information isn’t being returned from the mirror node, specifically two system views contained within the MSDB database:
sys.database_mirroringsys.databases
Examining the permissions on those views show that it is blank by default, which means that unless you have sysadmin role within SQL, you cannot access those views.
The Fix?
Based on MSDN information, it appears that there is no way around this. In order for this cmdlet to succeed and correctly return the information, the account running the cmdlet must be granted the sysadmin role within SQL. Many DBA’s and InfoSec leads would strongly argue against this configuration and in many aspects I would agree that having more rights than absolutely necessary is a bad idea. In this case though, the data within SQL is very low-risk and so long as Lync administrators understand the risk, it is safe to allow the rights. Put the Lync/Skype databases on dedicated servers and allow this to be an acceptable exception to an otherwise sound “least privilege” rule.
Test-CsDatabase returns ‘Exception has been thrown by the target of an invocation’
Test-CsDatabase is a very useful cmdlet that administrators should be able to execute to determine how the current databases and their schema versions match up with the expected version (based on the version of the Core Components installed on the machine running the cmdlet). Usually you would use this cmdlet to verify if the back end databases need to be updated after you’ve installed updates via the LyncServerUpdateInstaller.exe during a scheduled patch cycle. If you too heavily crank down on SQL security hardening, however, it will prevent the cmdlet from executing:
Test-CsDatabase: Command execution failed: Exception has been thrown by the target of an invocation.
Using the -report parameter on the cmdlet shows the additional information that proved to be critical in finding root cause:
Error: The EXECUTE permission was denied on the object 'xp_instance_regread'
It was very clear through the HTML report that permissions were not allowed on built-in SQL stored procedures and views that were required for the cmdlet to execute.
The Fix?
A quick web search turns up more than a few instances of application problems or other issues as a result of this error. This customer’s InfoSec and DBA team had made multiple changes to the Public Role within SQL which changed the default SQL configuration to be much more strict. This actually resulted in some other issues as well that weren’t specific to Lync but manifested itself in other ways within SQL Management Studio. Microsoft does have some documentation that talks about what can go wrong when you change the way the Public Role is configured, which is available both within MSDN and TechNet.
What is very clear in the articles is that unintended consequences can result from making these changes. While the practice of “least privilege” is a worthy one, I question the need to drastically change the SQL configuration for the purposes of Lync Server and/or Skype for Business Server. In this instance, the customer’s InfoSec and DBA team had to walk back their Public Role changes on the SQL instances hosting the Lync databases so the cmdlet would operate correctly. While I’m not a DBA, it certainly seems that this change that doesn’t really offer much security in return. My personal advice: limit (or completely prevent) changes to the Public Role configuration for your Lync SQL Back End environment.
Third-Party Anti-Virus Protection Within SQL Server
Antivirus programs are one of those “love to hate” things in IT – we can’t live without them but they often result in more problems than we’d like. Each vendor has their own “special sauce” and features that are included to keep us “more safe”, but customers should exercise extreme caution in Lync Server and/or Skype4B Server deployments. Most administrators set exclusions to prevent antivirus engines from scanning Lync Server related executables, including things like SQL instance files, but sometimes setting scanning exclusions isn’t enough. This particular issue was found as a direct result of running Key Health Indicator analysis on a customer’s Lync Server 2013 environment and detecting high numbers for SQL-related Counters:
| Counter Name | Min | Max |
| \LS:Usrv – SharedDBStore\Usrv – Sproc Latency (msec) | ||
| ************* | 0.00 | 989.00 |
| ************* | 0.00 | 289.00 |
| ************* | 0.00 | 560.86 |
While it seemed like everything else was in order it took more than a few discussions around disk I/O and other server resources to arrive at the ultimate solution.
The Fix?
Some Anti-Virus vendors actually inject the antivirus engine into SQL itself when SQL instances are installed on the target machine. This results in the antivirus engine being inside the SQL Server memory space and can also result in the antivirus engine examining and detouring API calls within SQL. This configuration is not supported and Microsoft has articles describing all the bad stuff that can happen:
Performance and consistency issues when certain modules are loaded into SQL Server address space
Detours or similar techniques may cause unexpected behaviors with SQL Server
Lync Server and Skype4B Server require very fast SQL operation and any slowness in SQL can have crippling effects into the stability and operation of Lync and/or Skype. For this particular customer, their DBA and SysAdmin discovered that the servers didn’t have Sophos configuration in place to prevent loading of the antivirus engine into SQL Server’s memory space:
Sophos Endpoint Security and Control and Microsoft Detours
The registry keys were configured on each of the servers and each server was rebooted. Following the reboots, Key Health Indicator analysis showed much different numbers for SQL-related Counters:
| Counter Name | Min | Max |
| \LS:Usrv – SharedDBStore\Usrv – Sproc Latency (msec) | ||
| ************* | 0.00 | 301.89 |
| ************* | 0.00 | 100.00 |
| ************* | 0.00 | 123.50 |
While the numbers are still higher than the Microsoft recommended value of 100ms, there was a noticable improvement to the operation within the Lync Server environment. Important to note is that while Sophos says that no known issues are created by their extra security, performance is absolutely impacted and can be measured quantifiably. Lync Server is a real-time workload and antivirus is not typically friendly to that cause, so extra caution is required if you choose to implement enhanced antivirus engine features on your servers. My personal advice: don’t configure anything beyond basic antivirus scanning on your Lync Servers and/or Skype4B Servers.
Bottom Line
Many of the issues above were the result of SQL configurations that were required by IT auditors or IT security professionals who dictated changes deemed necessary due to regulatory requirements and/or security best practices. I am again NOT advocating the complete removal of security practices but instead believe we should take a step back and ask simple logical questions about “what do we gain from this change?” and “does the vendor support this?” and “it is really required?”. Avaya or Cisco implementations don’t support significant changes to the SQL implementations utilized there, so it is not logical to expect that Microsoft would support it either. There are absolutely scenarios where heightened scrutiny is required, especially in regards to PII or Financial data, but I question the overall need to greatly restrict SQL configurations for Lync Server and Skype for Business Server given that the databases aren’t storing data that require heightened security. In environments that require significant SQL alterations, make sure you identify issues in a test environment first, document changes to satisfy audit requirements and then implement within production. Microsoft outlines the permissions that are required for SQL based off of default SQL configurations, so it is very difficult to plan ahead for changes that may be required if SQL has been considerably altered. The KISS principal is still a valuable mindset and should be used by default instead of attempting to complicate a solution with changes that are not really required!