2

We have some deployment scripts that use a combination of dacpacs through sqlpackage and straight scripts using sqlcmd.

When I execute the sqlpackage dac\sqlpackage.exe /p:BlockOnPossibleDataLoss=False /p:BackupDatabaseBeforeChanges=True /a:Publish /sf:.\somedeploy.dacpac /tcs:"Data Source=MyDB;Initial Catalog=MyCatalog;Trusted_Connection=True;"

I get an error message *** Could not deploy package. Unable to connect to target server. I have login auditing turned on in sql and the message I am getting is Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: 111.222.223.224]

However, I can run our sql scripts through sqlcmd no problem by using the -E argument. sqlcmd -i Permissions\CreateSqlUser.sql -S MyDB -E

I am running from a commandline that is set to "Run As Administrator" and am currently signed in as the admin. This is running on a Windows Server 2012 VM. I am using sql 2014. We have other installs that haven't had problems but this is our first 2014 install so we are trying to determine if something changed or if we are missing something and if so what.

If I run from profiler I don't see any audit log error messages. I checked the filters and what events I'm listening for and they all look good. Inside sql when I run exec sys.sp_readerrorlog 0, 1, 'Login Failed' I do get the messgaes to show up. (I might be doign something wrong in profiler). Also they show up in the system event logs. I'm not seeing anyplace where it tells me anything about the user attempting to login though.

If don't use trusted connection for sqlpackage and instead use a username and password it works fine. However, part of what our deployment script does is create the users so our preference would be to not do this.

  • This definitely looks like an authorization problem. I see that you are using /sf:.\somedeploy.dacpac. Have you examined the settings to see if a particular user is specified? (It would be /SourceUser: if full parameter names were used.) – RLF Jul 31 '14 at 17:59
  • The same package works in a production deployment but all of our production deployments are SQL Server 2012 or 2008. I'm setting up a VM with 2014 for testing purposes. In regards to a particular user being specified I am not seeing anything specified unless I am looking in the wrong place. I am looking at the project settings in Visual Studio. Thanks for the response. – CodeHulk Aug 1 '14 at 11:18
  • Not sure if this is still an issue, or of the exact reason behind this behavior, but I can point out two things if it helps: 1) SqlPackage.exe ignores the Trusted_Connection / Integrated Security keywords and assumes True / Yes if UserID and Password are not supplied. You can set Trusted_Connection to False and it still works. 2) One difference is that SqlPackage uses .NET SqlClient to connect while SQLCMD uses ODBC. So it might be how SqlClient is handling Impersonation and/or the protocol. You might want to look at this: support.microsoft.com/en-us/kb/909801 – Solomon Rutzky Nov 6 '15 at 17:46
  • I get that error when I use SqlPackage.exe from DacFX 120 when trying to publish to SQL 2016. If I change to use DacFX 130 then it works fine. I don't see Login Failed errors though: SQL Profiler for me was showing the connection established fine, a few statements run, and then the error shown in the console. So in my case the error message is misleading and really it's saying its unable to run against that db. I didn't try changing the Compatibility Level of the database down to 120 to see if that worked, but worth a go if this is the same problem you're having. Else install latest DacFX. – Rory Jul 13 '16 at 11:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.