As i’ve gone through numerous enterprise edition deployments everyone is unique but here is a few issues back-end SQL server issues i’ve ran into when deploying Enterprise Edition.
Microsoft Recommends hardening SQL server this means a couple of things:
1. Changing the port number from the default tcp port of 1433, when the port is changed the Lync Server 2013 deployment does not know how to contact the server when trying to publish the topology or future communications with the back-end SQL database.
Before you start your deployment on every Lync Server 2013 Front End server:
- Open windows explorer and browse to C:\Windows\System32\CliConfig.exe
- Click on the Alias tab
- Click Add
- Click TCP/IP
- Enter the SQL server Alias name ( SQLSERVER Name or if you have created a CNAME for the Lync Database use the CNAME ie LyncDatabase.local)
- Enter the SQL server name, same as above. If you are using an instance name then use the format : SERVERNAME\InstanceName
- Enter the port number that the SQL server has been changed to
- Click Ok twice
Now when during your topology deployment use only the Server Alias name when specifying a SQL server, obviously ensure that the account that you are using has appropriate privileges on the SQL for your reference here is the technet article: https://technet.microsoft.com/en-us/library/gg398375(v=ocs.15).aspx
2. Renaming or removing the built-in SA account
Microsoft also recommends renaming or removing the built-in SA account when you do this and try and publish the topology for the first time, it will fail stating that there was no SA account found on the SQL server and it will not create your databases.
This is a hard one to explain to DBA’s since they are told best practice as above states, but fear not its not as bad as you think. As long as the account your using in the topology has full access to SQL all you need to do is:
- Log onto your SQL server
- Open SQL Management Studio
- Connect to your SQL Instance
- Expand Security -> Logins
- Right Click Logins -> New Login
- Login Name: sa
- Select SQL Server Authentication
- Provide a password
- Click Ok
We do not need to provide any permissions the SA account it just actually has to be available on the server if you right click on the sa account and click properties you can see that the account only has Public access.
3. Disable Named-Pipes
When Named-Pipes is disabled on the SQL server there are certain powershell cmdlets that through an error, below is one of the examples that states that it cannot connect to the back-end SQL server even though you are able to publish the topology and you have configured your SQL Alias’s in the cliconfig.exe as stated above.
<Update-CsAddressBook : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was
not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider,
error: 40 – Could not open a connection to SQL Server)
At line:1 char:1
+ Update-CsAddressBook -Fqdn pool.domain.com -Force -Verbose
+ CategoryInfo : InvalidOperation: (Microsoft.Rtc.Common.Data.DBCore:DBCore) [Update-CsAddressBook], SqlConnectionException
+ FullyQualifiedErrorId : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found
or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes
Provider, error: 40 – Could not open a connection to SQL Server),Microsoft.Rtc.Management.AddressBook.Cmdlet.UpdateAddressBookCmdlet>
The only way to fix this is to enable Named-Pipes on the SQL server or SQL Server Instance and restart the SQL Server Service or Service Instance.