Unable to connect to SQL Server via Windows Authentication even when tenticle is running as domain user

ryan_helms · 20 March 2015 02:56

Support,

I am changing all of my connection strings to use Windows Authentication to connect and update the database during our deployment. However, I keep getting the following error when the deployment runs:

===========================================================================================

Connection String: < Server=XXXXXXX;Database=XXXXXXX;Trusted_Connection=yes;>
Migrate Path: C:\Octopus\Applications\Development\UniRush.Prepaid.Database\0.0.5556.39213_1\migrate.exe
Working Dir: C:\Octopus\Applications\Development\UniRush.Prepaid.Database\0.0.5556.39213_1
Info 22:49:32
VERBOSE: Target database is: ‘Prepaid’ (DataSource: sqldev, Provider: System.Data.SqlClient, Origin: Explicit).
System.Data.SqlClient.SqlException (0x80131904): Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.Open() at System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch[TTarget,TInterceptionContext](TTarget target, Action2 operation, TInterceptionContext interceptionContext, Action3 executing, Action3 executed) at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.Open(DbConnection connection, DbInterceptionContext interceptionContext) at System.Data.Entity.SqlServer.SqlProviderServices.<>c__DisplayClass33.<UsingConnection>b__32() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass1.<Execute>b__0() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func1 operation)
at System.Data.Entity.SqlServer.SqlProviderServices.UsingMasterConnection(DbConnection sqlConnection, Action1 act) at System.Data.Entity.SqlServer.SqlProviderServices.DbCreateDatabase(DbConnection connection, Nullable1 commandTimeout, StoreItemCollection storeItemCollection)
at System.Data.Entity.Migrations.Utilities.DatabaseCreator.Create(DbConnection connection)
at System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
at System.Data.Entity.Migrations.Console.Program.Run()
at System.Data.Entity.Migrations.Console.Program.Main(String[] args)
ClientConnectionId:c2ec2e11-3689-430e-ade1-eb82a65b96ff
ERROR: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
Fatal 22:49:32
PowerShell script returned a non-zero exit code: 1
Tentacle version 2.6.4.951

===========================================================================================

I have setup the tentacle service to run under a domain account that has the proper permissions on the sql server and from the look of it, it doesn’t even look like the tentacle is running under the domain user context. If I run the service as Local System, I get the same error. Is there something special that I need to do to enable the service to run under the context of the service account? I’'ve been pulling my hair out for hours, so I figured this was my next step.

Thank you!

Ryan Helms
RushCard

Dalmiro · 20 March 2015 15:33

Hi Ryan,

Thanks for reaching out. Everything makes sense according to your description. Could you please run the following command on that tentacle using the Script Console in Octopus?

$env:USERDOMAIN + "\" + $env:USERNAME

This will tell us exactly under which user is the process running. This way we can completely rule out this being a user account issue.

Thanks!

Dalmiro.

ryan_helms · 20 March 2015 18:41

Hello,

I ran the script that you requested. The output is below:

Running script on 1 machines.
Info 14:22:14
The script will run on the following machines:
Info 14:22:14

CB-DEVDB at https://sqldev:10933/
Info 14:22:16
The script ran on all machines successfully.
Run script on: CB-DEVDB at https://sqldev:10933/
Info 14:22:14
Sending script to CB-DEVDB at https://sqldev:10933/ with SQUID SQ-CB-DEVDBIPS-1E3C7DEA…
Info 14:22:14
Running script on "CB-DEVDB"
Info 14:22:16
Script run completed successfully.
Tentacle script execution
Info 14:22:16
UNIRUSH\OctopusDbSvcUser
Info 14:22:16
============================================== PowerShell exit code: 0 ==============================================

So it looks like the user is being passed through. I’m at a loss. I have verified database connectivity via SSMS, running at the domain user.

Thanks!

From: Dalmiro Grañas [mailto:tender2+dc521f3a1c@tenderapp.com]
Sent: Friday, March 20, 2015 11:34 AM
To: Ryan Helms
Subject: Re: Unable to connect to SQL Server via Windows Authentication even when tenticle is running as domain user. [Problems #31730]

// Please reply above this line

From: Dalmiro Grañas (Support staff)

Hi Ryan,

Thanks for reaching out. Everything makes sense according to your description. Could you please run the following command on that tentacle using the Script Console in Octopushttp://docs.octopusdeploy.com/display/OD/Script+Console?

$env:USERDOMAIN + “” + $env:USERNAME

This will tell us exactly under which user is the process running. This way we can completely rule out this being a user account issue.

Thanks!

Dalmiro.

On Fri, Mar 20 at 02:56 AM, ryan.helms wrote:

Support,

I am changing all of my connection strings to use Windows Authentication to connect and update the database during our deployment. However, I keep getting the following error when the deployment runs:

===========================================================================================

Connection String: < Server=XXXXXXX;Database=XXXXXXX;Trusted_Connection=yes;>
Migrate Path: C:\Octopus\Applications\Development\UniRush.Prepaid.Database\0.0.5556.39213_1\migrate.exe
Working Dir: C:\Octopus\Applications\Development\UniRush.Prepaid.Database\0.0.5556.39213_1
Info 22:49:32
VERBOSE: Target database is: ‘Prepaid’ (DataSource: sqldev, Provider: System.Data.SqlClient, Origin: Explicit).
System.Data.SqlClient.SqlException (0x80131904): Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.Open() at System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch[TTarget,TInterceptionContext](TTarget target, Action2 operation, TInterceptionContext interceptionContext, Action3 executing, Action3 executed) at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.Open(DbConnection connection, DbInterceptionContext interceptionContext) at System.Data.Entity.SqlServer.SqlProviderServices.<>c__DisplayClass33.<UsingConnection>b__32() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass1.<Execute>b__0() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func1 operation)
at System.Data.Entity.SqlServer.SqlProviderServices.UsingMasterConnection(DbConnection sqlConnection, Action1 act) at System.Data.Entity.SqlServer.SqlProviderServices.DbCreateDatabase(DbConnection connection, Nullable1 commandTimeout, StoreItemCollection storeItemCollection)
at System.Data.Entity.Migrations.Utilities.DatabaseCreator.Create(DbConnection connection)
at System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
at System.Data.Entity.Migrations.Console.Program.Run()
at System.Data.Entity.Migrations.Console.Program.Main(String[] args)
ClientConnectionId:c2ec2e11-3689-430e-ade1-eb82a65b96ff
ERROR: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
Fatal 22:49:32
PowerShell script returned a non-zero exit code: 1
Tentacle version 2.6.4.951

ryan_helms · 23 March 2015 21:02

Hello Dalmiro,

Do you have any additional information on this issue? Of course, we just recently purchased our company license the week before this issue came up. Is there a way to get phone or quicker support for license holders?

Thanks,
Ryan Helms

From: Dalmiro Grañas [mailto:tender2+dc521f3a1c@tenderapp.com]
Sent: Friday, March 20, 2015 11:34 AM
To: Ryan Helms
Subject: Re: Unable to connect to SQL Server via Windows Authentication even when tenticle is running as domain user. [Problems #31730]

// Please reply above this line

From: Dalmiro Grañas (Support staff)

Hi Ryan,

Thanks for reaching out. Everything makes sense according to your description. Could you please run the following command on that tentacle using the Script Console in Octopushttp://docs.octopusdeploy.com/display/OD/Script+Console?

$env:USERDOMAIN + “” + $env:USERNAME

This will tell us exactly under which user is the process running. This way we can completely rule out this being a user account issue.

Thanks!

Dalmiro.

On Fri, Mar 20 at 02:56 AM, ryan.helms wrote:

Support,

I am changing all of my connection strings to use Windows Authentication to connect and update the database during our deployment. However, I keep getting the following error when the deployment runs:

===========================================================================================

Connection String: < Server=XXXXXXX;Database=XXXXXXX;Trusted_Connection=yes;>
Migrate Path: C:\Octopus\Applications\Development\UniRush.Prepaid.Database\0.0.5556.39213_1\migrate.exe
Working Dir: C:\Octopus\Applications\Development\UniRush.Prepaid.Database\0.0.5556.39213_1
Info 22:49:32
VERBOSE: Target database is: ‘Prepaid’ (DataSource: sqldev, Provider: System.Data.SqlClient, Origin: Explicit).
System.Data.SqlClient.SqlException (0x80131904): Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.Open() at System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch[TTarget,TInterceptionContext](TTarget target, Action2 operation, TInterceptionContext interceptionContext, Action3 executing, Action3 executed) at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.Open(DbConnection connection, DbInterceptionContext interceptionContext) at System.Data.Entity.SqlServer.SqlProviderServices.<>c__DisplayClass33.<UsingConnection>b__32() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass1.<Execute>b__0() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func1 operation)
at System.Data.Entity.SqlServer.SqlProviderServices.UsingMasterConnection(DbConnection sqlConnection, Action1 act) at System.Data.Entity.SqlServer.SqlProviderServices.DbCreateDatabase(DbConnection connection, Nullable1 commandTimeout, StoreItemCollection storeItemCollection)
at System.Data.Entity.Migrations.Utilities.DatabaseCreator.Create(DbConnection connection)
at System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
at System.AppDomain.DoCallBack(CrossAppDomainDelegate callBackDelegate)
at System.Data.Entity.Migrations.Console.Program.Run()
at System.Data.Entity.Migrations.Console.Program.Main(String[] args)
ClientConnectionId:c2ec2e11-3689-430e-ade1-eb82a65b96ff
ERROR: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
Fatal 22:49:32
PowerShell script returned a non-zero exit code: 1
Tentacle version 2.6.4.951

Dalmiro · 24 March 2015 13:55

Hi Ryan,

Sorry for the delay on my reply. Could you tell me if the DB and the Tentacle are on the exact same domain, or if they are on domains that trust each other?

It might be best for us to schedule a call with screen sharing to troubleshoot this live. If you are ok with this, please pick up a date/time from this link

https://octopusdeploy.acuityscheduling.com/schedule.php

Thanks

Dalmiro.

ryan_helms · 24 March 2015 15:10

Hello Dalmiro,

I went ahead and scheduled a call for tomorrow @ 830PM EST via your scheduling application. I look forward to the help and I appreciate the response. It’s nice to know the personal assistance is available. Excellent work and looking forward to it!

Thanks!
Ryan

From: Dalmiro Grañas [mailto:tender2+dc521f3a1c@tenderapp.com]
Sent: Tuesday, March 24, 2015 9:56 AM
To: Ryan Helms
Subject: Re: Unable to connect to SQL Server via Windows Authentication even when tenticle is running as domain user. [Problems #31730]

// Please reply above this line

From: Dalmiro Grañas (Support staff)

Hi Ryan,

Sorry for the delay on my reply. Could you tell me if the DB and the Tentacle are on the exact same domain, or if they are on domains that trust each other?

It might be best for us to schedule a call with screen sharing to troubleshoot this live. If you are ok with this, please pick up a date/time from this link

https://octopusdeploy.acuityscheduling.com/schedule.php

Thanks

Dalmiro.

On Mon, Mar 23 at 09:02 PM, ryan.helms wrote:

Hello Dalmiro,

Do you have any additional information on this issue? Of course, we just recently purchased our company license the week before this issue came up. Is there a way to get phone or quicker support for license holders?

Thanks,
Ryan Helms

From: Dalmiro Grañas [mailto:tender2+dc521f3a1c@tenderapp.com]
Sent: Friday, March 20, 2015 11:34 AM
To: Ryan Helms
Subject: Re: Unable to connect to SQL Server via Windows Authentication even when tenticle is running as domain user. [Problems #31730]

// Please reply above this line

From: Dalmiro Grañas (Support staff)

Hi Ryan,

Thanks for reaching out. Everything makes sense according to your description. Could you please run the following command on that tentacle using the Script Console in Octopushttp://docs.octopusdeploy.com/display/OD/Script+Console?

$env:USERDOMAIN + “” + $env:USERNAME

This will tell us exactly under which user is the process running. This way we can completely rule out this being a user account issue.

Thanks!

Dalmiro.

On Fri, Mar 20 at 02:56 AM, ryan.helms wrote:

Support,

I am changing all of my connection strings to use Windows Authentication to connect and update the database during our deployment. However, I keep getting the following error when the deployment runs:

Damian_Brady · 26 March 2015 01:13

Hi Ryan,

We missed you on the call this morning!

I’ve done a bit of research, and this issue can come up if there’s a problem with network configuration. The tentacle may not be able to connect if it can’t properly locate the SQL box by its fully qualified domain name.

Can you have a look at this blog post to see if it helps? If not, some information about your network configuration would be useful - is the SQL instance on the same machine as the tentacle? Same domain? Can you connect to the SQL Server with that account from the machine that’s running the tentacle? Could a firewall be blocking port 1433?

Let me know how you go.

Damian

ryan_helms · 26 March 2015 01:50

Damien,

I was waiting for connectivity / meeting information from you for the call. We need to reschedule this for tomorrow. This issue is a blocker for us, be g a financial institution, we are unable to use Sql accounts for authentication for PCI reasons.

If you are available now, we can also do it now.

Thanks!

Sent via the Samsung GALAXY S® 5, an AT&T 4G LTE smartphone

Damian_Brady · 26 March 2015 01:55

Hi Ryan,

I sent an email with connection details, but it must not have got through!

I can do it now if you’re free. Are you able to send me an email to damo@octopusdeploy.com so I can reply with another GoToMeeting session?

Thanks,
Damian

Damian_Brady · 26 March 2015 02:34

Hi Ryan,

Thanks for the chat and I’m glad we got it resolved. I’m also happy to hear you’re enjoying the product!

One thing I forgot to mention (regarding using Octopus without the UI) is the Client library. It’s a direct wrapper for the REST API so it is feature-complete. Octo.exe is only designed to do some basic tasks.

Thanks,
Damian