sql server - New-SPConfigurationDatabase cannot connect to database/master unless I am logged into Windows desktop session

Question

This one is a doozy. Note, I'm using Ansible to automate, and that's part of the issue.

My ansible playbook fails with the following if I execute it remotely without being logged into the server:

"stderr": "New-SPConfigurationDatabase : Cannot connect to database master at SQL server at  SERVERNAME.fqdn. 
The database might not exist, or the current user does not have permission to connect to it.

I have a step in my playbook that confirm I am an AD user with the correct permissions on the database.

Furthermore, this playbook works if I happen to be logged into the server (target, where this is running) while the playbook runs. Note, there should be nothing linking the logged-in session to the ansible session, but it's like Active Directory (Kerberos?) only trusts this user when it's "really" logged in.

I did another quick test to see what SQL thought the user was trying to connect, as another task in the playbook:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = tcp:SERVERNAME; Database = master; Authentication=Active Directory Integrated;Encrypt = False;TrustServerCertificate=True;"
$SqlConnection.open()

and when I'm logged into the desktop it connects without error. When I'm not logged into the desktop simultaneously, it reports fails with Login failed for user 'NT AUTHORITY\ANONYMOUS "

Oh, and the ansible task is running with elevated privs; I don't understand why the SQL client seems to change it's mind which credentials to use based on whether the account is logged into with RDP.

question from:https://stackoverflow.com/questions/66054500/new-spconfigurationdatabase-cannot-connect-to-database-master-unless-i-am-logged

Answer 1

Waitting for answers