A couple of weeks ago I asked here how to get a sample database into Azure SQL. I got great feedback and was able to get the AdventureWorksLT database into Azure SQL. However, as often happens where I work, I was pulled away to work on other, urgent, tasks. 🙁
So, I'm only now able to get back to this for an hour or so. I'm just trying to connect to it using SSMS. I put in the URL to the server, then I have tried both Azure Active Directory - Password and Azure Active Directory - Integrated. Neither has worked. I got the following error message:
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64)
I suspect that the issue is network based. That I'll have to open a help ticket to get some rule created or something like that. But I'm ignorant enough of networking issues that I don't know what to ask for. I've investigated this issue and found this web site. One of the suggestions there was to either change my anti-virus or disable it. That ain't gonna happen, trust me. So, what should I ask for in order to allow me access to the database I've put into Azure SQL from my work desktop/laptop?
Kindest Regards, Rod Connect with me on LinkedIn.
December 15, 2021 at 1:10 pm
Hey Rod. First guess, have you poked a hole in the firewall so your account can get to the Azure database? That's the most common issue. You should be able to do this either through the Azure portal or from SSMS itself.
And no, you absolutely shouldn't need to disable your anti-virus. That's nuts. I've got anti-virus and VPN running on my laptop and I'm connecting up to AWS RDS & Azure SQL Database just fine.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 15, 2021 at 3:28 pm
Hey Grant, you're correct, I haven't opened a hole in my firewall. I'd be doing this from two separate places. Currently, I'm still working from home (thank God), so I'd need to do that from my router. This isn't something I've done a lot of. In fact, I think I've only done this once before, years ago. I can look up how to do that on my router. And of course, I might have to do something in Windows, too.
The other situation is from my work's firewall. I must sometimes remote to my desktop in the office, where I would bring up SSMS to get to the database in Azure SQL. Of course, in this instance I'll have to open a help ticket to get that firewall rule created.
The only thing is, I don't know what must be opened in either my router or at my desktop in the office. What needs to be opened so I can get to the Azure SQL Database using either SSMS or eventually the APIs or Web apps that we write?
Kindest Regards, Rod Connect with me on LinkedIn.
Not your router. Azure, by default, locks down your databases. You cannot connect to them without specifically allowing identified IP addresses. So, it's youre IP address that you need to add to Azure to get there from a remote connection like you're doing.
Go to the database on the portal. Right in the center at the top is "Set Server Firewall". That opens a new window. Right at the top there is "Add Client IP". Try that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 15, 2021 at 4:47 pm
DISREGARD THIS POST
I agree with Grant - you shouldn't need to do anything with the Antivirus. I also agree that it is likely going to be firewall related on either your local machine, the corporate network, or on the Azure side.
EDIT - I replied without refreshing to get the latest replies. Ignore my post as it has no value added to the thread.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 17, 2021 at 4:08 pm
Not your router. Azure, by default, locks down your databases. You cannot connect to them without specifically allowing identified IP addresses. So, it's youre IP address that you need to add to Azure to get there from a remote connection like you're doing.
Go to the database on the portal. Right in the center at the top is "Set Server Firewall". That opens a new window. Right at the top there is "Add Client IP". Try that.
WooHoo! That worked, Grant! I've got it set up with an IP address range that includes my IP, when I'm WFH (which I still am doing, thank God!)
I suppose I'd better define other rules for when I have to remote to my office machine, then run SSMS from there.
Kindest Regards, Rod Connect with me on LinkedIn.
December 17, 2021 at 4:26 pm
@Grant and everyone else, I'd like to ask a follow-up question. When I first tried to perform a SELECT to the SalesLT.Customer table in the AdventureWorksLT database I put into my Azure portal, it timed out. This doesn't surprise me. Subsequent queries ran fine. What do you recommend as a good timeout interval?
Kindest Regards, Rod Connect with me on LinkedIn.
December 17, 2021 at 4:38 pm
Yeah, not a clue. Most of the time, I get very good behavior out of Azure. Obviously not the same as something running locally, but very much the same as when I was connecting remotely to other servers. I'd say whatever you're used to ought to work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply