Symptom: When installing the Monitoring Agent for Lync 2010, I was receiving the following information in the “log” file:
> Deploying Monitoring Server Reports… This might take a few minutes.
The Monitoring Server is using SQL instance “myserver.mydomain”.
The data source is using SQL instance “(local)”.
The following URL will be used for deployment: https://myserver.mydomain:443/ReportServer
SQL Server logon credentials for “mydomain\myuser” already exist. Use the existing logon credentials.
“[QoEMetrics]” role “[ReportsReadOnlyRole]” has already assigned to “mydomain\myuser”.
“[LcsCDR]” role “[ReportsReadOnlyRole]” has already assigned to “mydomain\myuser”.
Start to deploy reports…
The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.
An error occurred when deploying Monitoring Server Reports. For details, see the log.
Turns out this was because I had accidentally forgot to add in the common name to my SSL certificate as a SAN address. Bottom line, the issue is that your SSL certificate is bad.
- Request a new SSL certificate
- Add in the Certificates snap-in
- Select Local Computer
- Expand Certificates->Personal->Certificates
- Right click, All Tasks->Request new Certificate…
- Select your policy
- Enter in the information you would like. Note, if you are adding a SAN address for the short name, make sure you add both the FQDN and the short name as DNS entries in the Alternative name box.
- Click on the Private Key tab, expand the little arrow and check Make private key exportable
- Click OK
- Click Enroll
- Click Start->All Programs–>Microsoft SQL Server 2008 R2->Configuration Tools->Reporting Services Configuration Manager.
- Click Connect
- Click Web Service URL
- Under SSL Certificate, select the appropriate certificate and click Apply
- At this point, you either saw a bunch of green check marks and you are good to go, or else you probably hit the dreaded “rouge SSL cert” error. To fix that, please see this link: http://jackstromberg.com/2013/01/sql-server-2008-r2-reporting-services-configuration-manager-create-certificate-binding-failed-hresult-0x80040238/
Symptom: When changing an SSL certificate inside of the SQL Server 2008 R2 Reporting Services Configuration Manager, you receive the following error:
Create certificate binding.
When you click on “Tell me more about the problem and how to resovle it.” you receive the following:
Microsoft.ReportingServices.WmiProvider.WMIProviderException: An SSL binding already exists for the specified IP address and port combination. The existing binding uses a different certificate from the current request. Only one certificate can be used for each IP address and port combination. To correct the problem, either use the same certificate as the existing binding, or remove the existing SSL binding and create a new binding using the certificate of the current request.
—> System.Runtime.InteropServices.COMException (0x80040238): Exception from HRESULT: 0x80040238
— End of inner exception stack trace —
at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.ThrowOnError(ManagementBaseObject mo)
at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.CreateSSLCertificateBinding(String application, String certificateHash, String ipAddress, Int32 port)
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.CreateSSLCertificateBinding(UrlApplication app, String certificateHash, String ipAddress, Int32 port)
This error really sucks and the reason behind it is that Microsoft just didn’t do a good job removing/unbinding SSL certificates from an interface. Luckily, I have the solution to get you up and going…
- Download the Windows Server 2003 Support Tools from Microsoft
- Install the tools on your local machine or on the server. You may get a warning about incompatibility if you install it on your Windows 7 machine. I ignored this and things seem to work fine 😛 Just note if you do this, you will need to copy the following folder from your local machine to the server with SQL Server Reporting Services Configuration Manager: C:\Program Files (x86)\Support Tools
- Open up a command prompt with Administrator privileges on the server with SQL Server 2008 R2 Reporting Services Configuration Manager
- Navigate to the following directory (if you copied the folder from your local machine, browse to the appropriate directory you copied the support tools to):
- cd “C:\Program Files (x86)\Support Tools”
- Launch SQL Server 2008 R2 Reporting Services Configuration Manager
- Start->All Programs->Microsoft SQL Server 2008 R2->Configuration Tools->
Reporting Services Configuration Manager
- Click on Web Service URL
- Click on the Advanced… button
- Remove any items listed inside of the “Multiple SSL Identities for the Report Server Web Service” box.
- Click OK
- Go back to your command prompt with the Administrator privileges and execute the following commands to unbind the old SSL certificate
- netsh http delete sslcert ipport=[::]:443
- You should see something like “SSL Certificate successfully deleted” — If not, that is fine
- httpcfg delete ssl /i 0.0.0.0:443
- You should see something like “HttpDeleteServiceConfiguration completed with 0.” — If not, that is fine as long as the command above said it removed a certificate
- Go back to the Reporting Services Configuration Manager and select your SSL certificate in the SSL Certificate dropdown.
- Click Apply
At this point, your certificate should have bound to the interface successfully.
Hope this helps someone!
If you are trying to login to a SQL server and you are receiving just the generic 18456 error, here is how to figure out what is going on.
- Open up the Microsoft SQL Server Management Studio tool.
- Login with an account that has administrative privileges.
- Click on Security and make sure under “Login Auditing” you have at least Failed logins only checked or higher.
- Click OK.
- Try to login with your account on another server to trigger the 18456 Error
- Go back to the SQL Server and open up Event Viewer (Start->Administrative Tools->Event Viewer)
- Click on Windows Logs->Application
- You should see Event ID 18456 inside of your logs (It won’t be Critical or Error level, just Information).
- Double click on your event, and it should give you some information on why the account is unable to login.
For example, in my case, I was attempting to use SQL authentication on the server, and the log told me the server was only setup for Windows Authentication (sure enough, it was! :P).
Hope this helps!