0

I'm in the Azure Portal UI attempting to connect our new Azure Search service to our SQL Server on our Azure VM. The UI very simply asks me for a connection string, a username and password. I started by using the exact connection string that is used in all our .Net config files, with which there is no issue connecting. No matter how I tweak the connection string based on many threads I've read here at S/O (set Encrypt to False, set TrustServerCertificate to True) I get this error testing the connection:

"A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) Learn more about connecting to Azure SQL VMs, http://go.microsoft.com/fwlink/?LinkID=826562"

I must admit that the info in that MS link is like a foreign language to me. As none of those steps have ever been required for me to connect to this SQL Server, I continued my research which lead me to all the different aforementioned tweaks to the connection string.

As none of these tweaks worked, it seems the answer truly lies in that MS article - would someone be able to provide a step-by-step as I am completely new to certificates.

I'd also still fail to understand why I have no issues connecting to this SQL Server from any other app/service/util and only get this error here, but if it works it works and I won't complain!

Eugene Shvets
  • 4,561
  • 13
  • 19
Stpete111
  • 3,109
  • 4
  • 34
  • 74

1 Answers1

1

The basic issue here is that your SQL Server must have a certificate issued by a trusted certificate authority, not a self-signed cert typically used in intranet apps. I suspect the connection strings worked in other apps because they either don't use encryption or trust self-signed certs (that's what TrustServerCertificate setting does).

Please follow the directions in the article you linked - that's the most authoritative step-by-step guide I'm aware of.

Alternatively, if you don't want to configure certificate and ports, you can consider using Azure Data Factory to push data to Azure Search.

Eugene Shvets
  • 4,561
  • 13
  • 19
  • I definitely want to connect directly from Azure, but thank you for the suggestion for Data Factory. Regarding following the instructions in the link, this is what I'm having trouble with. Right off the bat, I don't understand step 1. "Check the properties of THE certificate..." What certificate?? What am I looking for once I use the tools they suggest. That first sentence is so vague, I'm already lost! Thanks. – Stpete111 Apr 14 '17 at 16:19
  • The SSL server certificate used for SQL Server. We'll try to make the article more clear. – Eugene Shvets Apr 14 '17 at 16:39
  • Thank you Eugene for your reply. When looking in certmgr, where do I find the SSL certificate used for SQL Server? Or is there an easier place for me to view the SQL Server SSL certificate? – Stpete111 Apr 14 '17 at 16:44