Connecting to Azure Sql Managed Instance

Question

Hello,

I am having connectivity issue after migrating on-prem Databases to Azure Sql Managed Instance.

I tried two way and getting slightly different error message:

Somehow I am confused as I am able to using SSMS and Azure data Studio via my local, I am able to connect to the Azure Managed Sql Instance.

I also tried to rum test connectivity using "test-netconnection" from my job scheduler server and able to get answer/connectivity.

I have also my windows AD login mapped with MS Azure Entra Id (AAD).

I thought if it had a issue with AD/AAD then Sql login also not working but I am able to access using Sql login via SSMS.

I am admin on the server/database and I also tried to pass my AAD account instead of Service account but still having issue when I tried to run the job.

Thanks for your help!

1) I have SSIS package and it has Config file which I have modified using Connection string showing into Portal as below:
 
[A] Sql login
=============
</Configuration>
Configuration ConfiguredType="Property" Path="\Package.Connections[Target DB].Properties[ConnectionString]" ValueType="String">
		<ConfiguredValue>Data Source=AzureMiDB.e1234a1a98c1.database.windows.net;Persist Security Info=False;Provider=SQLNCLI10.1;User id=sqlUser;Password=pwd1234;Initial Catalog=testdb;MultipleActiveResultSets=False;Encrypt=True;Connection Timeout=30;Application Name=SSIS-Package-{D02FC0E7-260E-411C-9699-17662F513AE5};Auto Translate=False;</ConfiguredValue>
	</Configuration>
Error:
=====error 0x80070035 "The network path was not found.".  This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
End Error
[B]
I also tried using AAD
Windows AD (now Azure AD)
===========================
<Configuration ConfiguredType="Property" Path="\Package.Connections[CnnMgr].Properties[ConnectionString]" ValueType="String">
    <ConfiguredValue>Data Source=AzureMiDB.e1234a1a98c1.database.windows.net;Initial Catalog=testdb;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication="Active Directory Integrated";</ConfiguredValue>
  </Configuration>
Error:
======
Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Server name cannot be determined.  It must appear as the first segment of the server's dns name (servername.database.windows.net).  Some libraries do not send the server name, in which case the server name must be included as part of the user name (username@servername).  In addition, if both formats are used, the server names must match.".
End Error
2) I also tried to just call Managed Instance in one of my batch job as below:
SERVER: AzureMiDB.e1234a1a98c1.database.windows.net
Database: TestDB
Error:
=======
System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)