SSIS Deployment Issue

  • Michael_R_Harvey

    Valued Member

    Points: 62

    Hello:

    Problem:  Cannot deploy SSIS packages to AZURE VM/SQL Server Instance (or execute them via SSDT/VS) from a local laptop using a SQL Login. FYI:  Project Deployment Model is used.

    Background:  We moved our development SQL Server instance to an AZURE VM, and would like to avoid giving every developer RDP permissions access to the server.

    It seems that SSIS Projects can be only be deployed via a WINDOWS AD account while logged onto a server using an ISPAC file, or opening an SSIS Project on the server itself and deploying from there.

    See error specifics below but my question is this: is there a work-around so that we can connect to AZURE SQL SERVER instance using SQL LOGIN’s and deploy/run package from our development laptop, or must every developer have RDP/Windows AD access to the new development server?

    When we try to deploy SSIS Projects to the Integration Services Catalog from SSDT or VS, using a SQL Login we receive the following error message:

    ------------------------------

    TITLE: SQL Server Integration Services

    ------------------------------

    Operation 'Create' on object 'CatalogFolder[@Name='DNA_ODS']' failed during execution. (Microsoft.SqlServer.Management.Sdk.Sfc)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=15.0.17243.0+((SSMS_Rel).180502-2324)&LinkId=20476

    ------------------------------

    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Integrated Authentication.

    The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Integrated Authentication. (Microsoft SQL Server, Error: 27123)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.3281&EvtSrc=MSSQLServer&EvtID=27123&LinkId=20476

    ------------------------------

    Also tried to change connection type to Windows Authentication (in my case have more than adequate permissions) and received the following error message:

    ------------------------------

    TITLE: SQL Server Integration Services

    ------------------------------

    Failed to connect to server 172.21.2.5. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    ADDITIONAL INFORMATION:

    The target principal name is incorrect.  Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476

    ------------------------------

  • frederico_fonseca

    SSChampion

    Points: 14651

    one option is do the deployment through t-sql

    requires that bulk admin is granted to the devs (but not a big issue normally)

    use SSISDB
    declare @projectbinary as varbinary(max) = null
    declare @operation_id as bigint
    set @projectbinary = (select *
    from openrowset(bulk 'fullpath_to_.ispac', single_blob) as BinaryData
    )

    if (@projectbinary is not null)
    begin
    exec catalog.deploy_project @folder_name = 'foldername'
    , @project_name = 'projectname'
    , @project_stream = @projectbinary
    , @operation_id = @operation_id out
    end
    else
    begin
    print 'file not found'
    end

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply