locked
AcquireConnection Method Call to connection manager SmartSheetODBC Failed RRS feed

  • Question

  • Hello Experts,

    There is a peculiar problem I face when connecting to ODBC connection from the SSIS Packages, the connection fails if I logoff as me Domain\My NT Logon ID from the server.

    I have deployed SSIS package and scheduled to run from a SQL Server Agent Job.

    The ODBC is configured in server using my Domain\NT logon ID. The SQL Server Agent Job that calls the SSIS package runs okay when I am logged into the server. Also tried executing the package from SSISDB Catalog  it works perfectly. But when I logoff as me from the server the package fail to execute.

    ERROR : The AcquireConnection method call to the connection manager Smartsheet_User_DSN failed.

    I am not sure If I need to configure ODBC in the server as an Administrator?! I also opened ODBC Data Sources (64 Bit) as "Run as Administrator" and configure ODBC but no luck, Can anyone let me know what could be the problem please?

    Regards

    Senthil


    Saturday, June 30, 2018 8:31 AM

Answers

  • Hello Experts,

    There is a peculiar problem I face when connecting to ODBC connection from the SSIS Packages, the connection fails if I logoff as me Domain\My NT Logon ID from the server.

    I have deployed SSIS package and scheduled to run from a SQL Server Agent Job.

    The ODBC is configured in server using my Domain\NT logon ID. The SQL Server Agent Job that calls the SSIS package runs okay when I am logged into the server. Also tried executing the package from SSISDB Catalog  it works perfectly. But when I logoff as me from the server the package fail to execute.

    ERROR : The AcquireConnection method call to the connection manager Smartsheet_User_DSN failed.

    I am not sure If I need to configure ODBC in the server as an Administrator?! I also opened ODBC Data Sources (64 Bit) as "Run as Administrator" and configure ODBC but no luck, Can anyone let me know what could be the problem please?

    Regards

    Senthil


    Looks like what you've created is a User DSN. The service account which the SQL Agent uses will not be able to access it. So try creating a server DSN. Also make sure you use correct interface (32 /64 bit) to create the DSN based on how you've configured in your job( Use 32 bit runtime setting). If you created 32 bit DSN, it has to be checked and it should be left unchecked for 64 bit

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, July 2, 2018 7:58 AM

All replies

  • Hi,

    Please can anyone check my thread and respond.



    Monday, July 2, 2018 7:04 AM
  • Hello Experts,

    There is a peculiar problem I face when connecting to ODBC connection from the SSIS Packages, the connection fails if I logoff as me Domain\My NT Logon ID from the server.

    I have deployed SSIS package and scheduled to run from a SQL Server Agent Job.

    The ODBC is configured in server using my Domain\NT logon ID. The SQL Server Agent Job that calls the SSIS package runs okay when I am logged into the server. Also tried executing the package from SSISDB Catalog  it works perfectly. But when I logoff as me from the server the package fail to execute.

    ERROR : The AcquireConnection method call to the connection manager Smartsheet_User_DSN failed.

    I am not sure If I need to configure ODBC in the server as an Administrator?! I also opened ODBC Data Sources (64 Bit) as "Run as Administrator" and configure ODBC but no luck, Can anyone let me know what could be the problem please?

    Regards

    Senthil


    Looks like what you've created is a User DSN. The service account which the SQL Agent uses will not be able to access it. So try creating a server DSN. Also make sure you use correct interface (32 /64 bit) to create the DSN based on how you've configured in your job( Use 32 bit runtime setting). If you created 32 bit DSN, it has to be checked and it should be left unchecked for 64 bit

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, July 2, 2018 7:58 AM
  • Thanks Visakh, That was a silly mistake i did to create it as a User DSN, i have changed now and it seems to be working even after I logout from the server.

    Regarding 32/64 bit I have set it right so that shouldnt be a problem

    Monday, July 2, 2018 9:19 AM