We use Azure Active Directory (Azure AD) in my organisation, and we recently deployed Azure AD Domain Services AADDS) to handle a SQL Server 2019 we use internally.
My Azure AD domain is (fictitiously) contoso.com, while my AADDS is aaddscontoso.com. Microsoft strongly recommends that users do not create their managed domain (the AADDS deployment) with the same domain name as their Azure deployment. They also strongly recommend against using something like contoso.local. So of I went to create our AADDS as aaddscontoso.com and everything seems to be working perfectly.
The Azure AD works with AADDS, (at least with the Vanilla way we have set-up) is:
- All of all laptops, desktops, etc are joined to the Azure AD through the Azure AD join (the cloud method)
- We can access the managed domain and its hots (including the SQL server) in our Azure Network via Azure VPN. For instance, right now I am VPN into our Azure network and I can remote desktop to the SQL Server.
Here is where the problem starts. Today I just noticed that trying to deploy an SSIS package from Visual Studio from my laptop (whilst connected on VPN) won't work.
I tried using Windows Authentication and the authentication fails with the following error:
The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)
So I tried using the Active Directory Password Authentication option, then I got the following error:
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.) (Microsoft SQL Server, Error: -2146893019)
Then I tried to be clever and create a SQL Login with the right administrative rights and the connection worked. However it fails on the deployment of the package, saying that you cannot use a SQL account for this. At least this proves that the connection to the server is not the issue.
I THINK that this problem could be simplified as follows: I want to deploy an SSIS package from the PC in DOMAIN A to a SQL server found in DOMAIN B. How can I do that? (Granted that this is not really it, but SSIS seems to think it is).