0

Brand new Azure account. Created a vnet (10.25.0.0/16) and an SQL DB/Server (no AD, just an admin login: sadmin/***)

Using SSMS, I can connect the SQL server through its public endpoint (prompted for Azure login by SSMS. FW rule added on-the-fly) ✔

Now, I'm trying to connect through a private endpoint:

  • Created a VNet gateway and OpenVPN access: I am now connected to my vnet using OpenVPN ✔

  • Connected the SQL server to the VNet using a private endpoint (10.25.0.4)

  • Access to server through Azure resources enabled. No specific FW rules.

Using SSMS (on my computer) and SQL Server authentication (sadmin/***), I can connect the SQL server but login fails:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The target principal name is incorrect.) (Microsoft SQL Server, Error: -2146893022)

Also, telnet 10.25.0.4 1433 connects, which confirms the message above about successful connection.

Same error message even if I use a wrong password or username.

Note: I reset the sadmin password (through the portal) to make sure I use the correct password.

What's wrong? Note: I am neither a SQL server specialist nor a DBA. I may miss the obvious.

Improve this question
1
  • have you added that IP in the firewall rules? Commented May 2, 2020 at 14:01

4 Answers 4

Reset to default
1

I know this post is old but I had the exact same issue where I turned off public access to the Azure SQL and enabled private endpoints. Once I did this, all my connections were failing. After research, I found this helpful article here

Basically, when using private endpoints, access to Azure SQL gets confused of what is the correct full FQDN. So logging into SSMS I needed to add "@servernamehere" to my username to connect successfully. The "@servernamehere" is the actual name of the Azure SQL server.

For my applications, I had to update my connection string as well with the updated username and also set TrustServerCertificate to true; something like so:

"Server=tcp:XX.XXX.X.X,1433;Initial Catalog=MYDATABASE;Persist Security Info=False;User ID=UsernameHere@serverNameHere;Password=XXXXXXXXXXXXXX;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;"

I don't know how it all works behind the scenes but this is the only solution that worked for me at this time.

Improve this answer
0

If you're using DTU based, try to upgrade a little bit. E.g. 100 DTU

Improve this answer
2
  • How can this be related ? That would mean I have to pay more to be able to connect my server from a private point !? Commented May 3, 2020 at 15:31
  • @SergeWautier I've faced problems when trying to connect to SQL Database and it turns out someone created it with 50 DTU or so. I don't think it's a VNET or VPN problem but as I don't have access to the environment, I just shared this based on my previous experiences. Commented May 3, 2020 at 17:09
0

First verify the name of the Azure SQL Server is correct and the name of the database is correct. You should specify the name of the server (.database.windows.net) on the "Connect to server" dialog of SSMS and on the "Options" button, on the "Connect to database" field please provide the name of the database.

Now let's start by testing basic connectivity using the current IP of your Azure SQL Database server. Let's ping the name of the Azure SQL server. The ping will fail but it should also return the current IP of the SQL Azure Server if DNS resolution is working.

C:\>ping <myserver>.database.windows.net Pinging data.sn1-1.database.windows.net [65.55.74.144] with 32 bytes of data: Request timed out. Request timed out. Request timed out. Request timed out. Ping statistics for 65.55.74.144: Packets: Sent = 4, Received = 0, Lost = 4 (100% loss)

Now try to telnet to port 1433 using the IP address returned.

C:\>telnet 65.55.74.144 1433 Connecting To 65.55.74.144...

If Telnet is successful, the window will change to a completely blank screen. Try connecting with that IP using SSMS again. If telnet is not successful, try tracert or traceroute to identify at what step it fails to reach the Azure SQL Database server.

C:\>tracert 65.55.74.144 Tracing route to 65.55.74.144 over a maximum of 30 hops 1 2 ms 1 ms 1 ms 192.168.0.1 2 6 ms 5 ms 5 ms 10.33.34.50 3 5 ms 4 ms 5 ms 4-1-753.DLLSTX-LCR-07.verizon-gni.net [130.81.107.26] 4 8 ms 5 ms 5 ms so-6-2-0-0.DFW01-BB-RTR1.verizon-gni.net [130.81.28.208] 5 44 ms 43 ms 44 ms so-11-0-0-0.LCC1-RES-BB-RTR1-RE1.verizon-gni.net [130.81.17.40] 6 49 ms 44 ms 44 ms so-6-0-0-0.ASH-PEER-RTR1-re1.verizon-gni.net [130.81.10.90]
Improve this answer
2
  • Thnaks for your reply. you're mentioning the public hostname, which works (of course until I disabled it). I'm trying to access through the private endpoint. Commented May 3, 2020 at 15:29
  • You need to always use that name because the IP of your server can vary at any time. Commented May 3, 2020 at 19:59
0

You need to trust server certificate when connecting via IP address

enter image description here

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.