April 1, 2020 at 11:49 am
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 xxx.xx.x.x. (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
------------------------------
April 1, 2020 at 12:55 pm
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 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply