Is it possible to use Azure Data Studio to connect to on-prem SQL Server?
-
I'm getting this error when trying to create a connection to an on-prem SQL Server through Azure Active Directory - Universal with MFA authentication.
Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 35 - An internal exception was caught)
Thanks!
-
My guess is that it is not a problem with on-prem, it is a problem with MFA. Personally, I have never set up SSMS or ADS to work with MFA on a SQL instance, and I didn't even know you could. I can't imagine why you'd want MFA set up for your SQL instances though as the security can be handled outside of the SQL authentication chain. What I mean by that is that your SQL instances are NOT world facing and as such require either physical access to the building OR VPN access to your network. Since you have that layer protected (MFA for VPN and logging into the physical machines), do you need MFA for the database layer?
On top of that, how do the other applications connect to the SQL instance if MFA is required? Do the applications handle that?
Now, if that is a typo and MFA is NOT required to log into the SQL instances, I would review the logs. Look at both the SQL logs and the Windows/Linux server logs to see what they say happened around that time. It could be something (firewall, web filter, threat detection, etc) saw the connection as suspicious and shut it down. These likely won't be captured in the SQL or server logs, but doesn't hurt to look.
I'd also try connecting with a different tool such as SSMS just to confirm if it is a tool problem or something else. Another thought is it MAY be that ADS doesn't support the version of SQL Server you are connecting to. Offhand, I am not sure what versions of SQL Server it support, and I am unable to find any reliable documentation on that.
And my last question - you ARE connecting to a Microsoft SQL Server instance, correct? Possibly a dumb question, but if you are connecting to Oracle or PostgreSQL or MySQL, ADS is NOT the right tool for you.
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. -
Use Windows Authentication. Universal with MFA won't work on an on-prem database.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ - Michael L John wrote:
Use Windows Authentication. Universal with MFA won't work on an on-prem database.
That might very well be the issue. Okay, so how do I switch user to use "Windows Authentication"? If I choose to use Windows Authentication as my authentication type, I do not have an option to specify my Windows Server access credential in this window.
- Mr. Brian Gale wrote:
Since you have that layer protected (MFA for VPN and logging into the physical machines), do you need MFA for the database layer?
I don't have MfA for the database layer. It's for the Windows Server layer.
Mr. Brian Gale wrote:And my last question - you ARE connecting to a Microsoft SQL Server instance, correct? Possibly a dumb question, but if you are connecting to Oracle or PostgreSQL or MySQL, ADS is NOT the right tool for you.
I'm running ADS on a MacOS so I can't run SSMS. To connect to our on-prem SQL Server database, I have to RDP into the actual Windows Server of the SQL Server instance and launch SSMS. I'm RDP into a Windows Server with a AD server credential and not my regular desktop Windows AD account. All SQL Server instances are configured to allow only Windows Server AD credential.
- 2Charlie wrote:Michael L John wrote:
Use Windows Authentication. Universal with MFA won't work on an on-prem database.
That might very well be the issue. Okay, so how do I switch user to use "Windows Authentication"? If I choose to use Windows Authentication as my authentication type, I do not have an option to specify my Windows Server access credential in this window.
Are you authenticated with a domain? If so, you do not specify a user and password. SSMS knows your login and password, and passes it through to SQL.
If you are not connected and authenticated through a domain, then you will need to use SQL Authentication.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ - 2Charlie wrote:Mr. Brian Gale wrote:
Since you have that layer protected (MFA for VPN and logging into the physical machines), do you need MFA for the database layer?
I don't have MfA for the database layer. It's for the Windows Server layer.
Mr. Brian Gale wrote:And my last question - you ARE connecting to a Microsoft SQL Server instance, correct? Possibly a dumb question, but if you are connecting to Oracle or PostgreSQL or MySQL, ADS is NOT the right tool for you.
I'm running ADS on a MacOS so I can't run SSMS. To connect to our on-prem SQL Server database, I have to RDP into the actual Windows Server of the SQL Server instance and launch SSMS. I'm RDP into a Windows Server with a AD server credential and not my regular desktop Windows AD account. All SQL Server instances are configured to allow only Windows Server AD credential.
There should never really be a reason to RDP into a database server to do any kind of work, with the exception of making server level changes such as to the disks.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ - Michael L John wrote:
Are you authenticated with a domain? If so, you do not specify a user and password. SSMS know your login and password, and passes it through to SQL.
If you are not connected and authenticated through a domain, then you will need to use SQL Authentication.
Here's the scenario. I logged in to my computer using my regular domain account e.g. charlie. However, when I RDP into Windows Server with SQL Server instance installed, I use my Windows domain account e.g, charlie-server. However, since I'm using ADS, if I choose to use the Windows Authentication, it will automatically use my regular account which is charlie instead of the charlie-server account. And no, we do not configured our SQL Server instance to use sql server logins.
- Michael L John wrote:
There should never really be a reason to RDP into a database server to do any kind of work, with the exception of making server level changes such as to the disks.
I'm using a Macbook Pro so currently I need to RDP into a database server since SSMS does not run on Mac OS. That is also one of the reasons why I'm exploring Azure Data Studio.
- 2Charlie wrote:Michael L John wrote:
Are you authenticated with a domain? If so, you do not specify a user and password. SSMS know your login and password, and passes it through to SQL.
If you are not connected and authenticated through a domain, then you will need to use SQL Authentication.
Here's the scenario. I logged in to my computer using my regular domain account e.g. charlie. However, when I RDP into Windows Server with SQL Server instance installed, I use my Windows domain account e.g, charlie-server. However, since I'm using ADS, if I choose to use the Windows Authentication, it will automatically use my regular account which is charlie instead of the charlie-server account. And no, we do not configured our SQL Server instance to use sql server logins.
Have you tried "RUNAS"?
RUNAS /user:DOMAIN\username "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe"
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ - Michael L John wrote:
Have you tried "RUNAS"?
RUNAS /user:DOMAIN\username "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe"
Again, I'm on MacBook Pro. I don't think there is such thing as "RUNAS" in Mac OS.
-
I don't think SSMS is even available in Mac OS.
- 2Charlie wrote:
I don't think SSMS is even available in Mac OS.
Whoops. Missed that part. Sorry!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply