Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
Running Package from Azure File store connecting to SQL Server VM as destination not working. SQL VM 2022.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(316.8, 42%, 40%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EKM%3C/text%3E%3C/svg%3E)
Kriparam Mannaraj
0
Reputation points
We have a SQL Server 2022 VM in Azure. SSIS packages developed in the 2012 version are being migrated to this VM. Locally we loaded them to visual studio 2022 and after editing drivers and minor things, they work. when we push the packages as project to azure files and run it from pipelines in azure, we get this error.
"OperationErrorMessages": "6/27/2023 5:49:55 PM +00:00 : oledb:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.\r\nAn OLE DB record is available. Source: \"Microsoft SQL Server Native Client 11.0\" Hresult: 0x80040E4D Description: \"Login failed for user 'abcd'.\".\r\n\n6/27/2023 5:49:55 PM +00:00 : Data Flow Task:Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager \"172.28.100.18.MapleDb.SQLAnalysisAdmin\" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.\r\n\n6/27/2023 5:49:55 PM +00:00 : Data Flow Task:Error: OLE DB Destination failed validation and returned error code 0xC020801C.\r\n\n",
"LogLocation": "\\\\regencycentersstore.file.core.windows.net\\ssislogs\\114ef05d7b544fa7bc8731b05ff8fb46",
"effectiveIntegrationRuntime": "abcd-SSIS-IR (East US)",
"executionDuration": 23,
"durationInQueue": {
"integrationRuntimeQueue": 0
We have connected the SSIS IR to a vnet and the vm is also in the same VNET. we are able to connect to the database from SSMS in another local VM. SSIS package in local VM is able to connect to this database and runs successfully. what are the options.
Azure Data Factory
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(86.4, 36%, 18%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBM%3C/text%3E%3C/svg%3E)
Bhargava-MSFT 31,356 Reputation points Microsoft Employee Moderator2023-06-28T19:38:09.0233333+00:00 Welcome to the MS Q&A platform.
As per the error, ": "Login failed for user 'abcd'.".\r\n\n6/27/2023 5:49:55 PM +00:00 : Data Flow Task:Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "172.28.100.18.MapleDb.SQLAnalysisAdmin""
the login failed for the user 'abcd' when trying to establish a connection from the connection manager "172.28.100.18.MapleDb.SQLAnalysisAdmin".
Please check the credentials used to connect to the SQL Server database are correct. Ensure the username and password are correct, and the user has the necessary permissions to access the database.
I hope this helps.
-
Bhargava-MSFT 31,356 Reputation points Microsoft Employee Moderator
2023-06-29T16:15:12.66+00:00 Hello @Kriparam Mannaraj,
I am checking to see if you got a chance to look into my earlier response. Please let me know if you have any further questions.
-
Kriparam Mannaraj 0 Reputation points
2023-07-03T16:04:40.4433333+00:00 thanks Bhargava. the credentials are right, for some reason
- SSIS IR in ADF when running a package in the file store cannot reach the destination database
- in this case source and destination are the same SQL Server VM in Azure
- if we deploy the package in an ssis DB in a separate Azure SQL DB, it works. Not sure whats missing if its a file share !
Can you throw some light ? We have joined the SSIS IR and the VM to the same vnet. the SSIS IR and Self hosted IR proxy thing doesnt work for us. can you please check if you have had similar issues ? are there specific settings for it. please let me know
-
Bhargava-MSFT 31,356 Reputation points Microsoft Employee Moderator
2023-07-05T23:21:20.0933333+00:00 Thanks for the additional details.
It seems like the issue is related to the firewall settings on the SQL Server VM. When you run the SSIS package from the file store, the SSIS Integration Runtime needs to connect to the SQL Server VM over the network. If the firewall is blocking the connection, the package will fail.
Can you please check the firewall setting on the SQL VM and allow connections from ADF's IR?
and also, please check if the 1433 SQL port is opened on the SQL VM.
Sign in to comment
Sign in to answer