Accessing Azure SQL DB from an Azure VM Using the External IP

SQL Server External Access

Server / June 9, 2020

Please do not set TRUSTWORTHY ON unless absolutely necessary! And it should only be "necessary" when loading an Assembly that you did not build and cannot re-sign. And that mostly happens when loading .NET Framework libraries that aren't "supported" and hence not already in SQL Server's CLR host. Outside of those circumstances, you should not be setting the database to TRUSTWORTHY ON as it opens up a security hole.

Instead, it is much better to do the following:

USE [master]; CREATE ASYMMETRIC KEY [SomeKey] AUTHORIZATION [dbo] FROM EXECUTABLE FILE = 'C:\path\to\Some.dll'; CREATE LOGIN [SomeLogin] FROM ASYMMETRIC KEY [SomeKey]; GRANT EXTERNAL ACCESS ASSEMBLY TO [SomeLogin]; - or "UNSAFE" instead of "EXTERNAL ACCESS"

The above only needs to be done once per Instance, per key. So if you use the same snk / pfx file for all of your assemblies, then the steps shown above only need to be done once per SQL Server Instance; the number of Assemblies and databases containing those Assemblies does not matter.

This approach allows you to keep better security on the database (by keeping TRUSTWORTHY set to OFF) and allows for more granular control of which assemblies are even allowed to be set to EXTERNAL_ACCESS and/or UNSAFE (since you can separate by using different keys for signing and Logins based on those different keys).

However, if you must use the TRUSTWORTHY ON method, then the database owner does not need to be sa. The requirement is merely that the Login registered as the database owner has been granted either EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY (same two permissions shown above for the Asymmetric Key-based Login).

For a detailed walk-through of how to automate this via Visual Studio / SSDT, please see the following 3 articles (a 3-part series), also on SQL Server Central:

Also, since writing those 3 articles, I have come up with an easier method using T4 templates but have not had time to write that up yet. When I do, I will update this answer with a link to that article.

Source: stackoverflow.com