Server data connection using Windows Authenticate

[Vernon]

Hello,

I am running into issues setting up a data connection to a SQL Server using Windows AD.

Scenario:

User can run a workflow via gallery and able to run a workflow that access (read/write) files into the SQL server using their own windows AD credentials (as how they login to the server gallery)

Error 1:

I was able to create an OLEDB connection within the server "Add Other" data connections tab.

Using Designer, i was able to sync it and able to read the tables/files from the SQL server in Designer

When I try to publish the workflow, the validation step gives me this error: 

Error opening connect string: Microsoft OLE DB Provider for SQL Server: Login failed for user 'yyy'.\42000 = 18456 (where yyy is the server machine).

I published to the server anyway, and either

a) if i set it to REQURE USER CREDENTIALS, then my username and password is incorrect

b) if i set it to USE DEFAULT CREDENTIALS, then i get the error message as above

This is the connection string:

odb:Provider=SQLOLEDB.1;Initial Catalog=--;Data Source=-----------;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Integrated Security=SSPI

Error 2:

I am not able to create the server connection using the default page for "adding sql server connection"

I get this error using windows AD..

Error: Logon failure: the user has not been granted the requested logon type at this computer. (1385).

Error 3:

I was able to create an ODBC connection within the server "Add Other" data connections tab.

Using Designer, i was able to sync it and able to read the tables/files from the SQL server in Designer

When I try to publish the workflow, the validation step gives me this error: 

Error SQLDriverConnect: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

a) if i set it to REQURE USER CREDENTIALS, then my username and password is incorrect

b) if i set it to USE DEFAULT CREDENTIALS, then i get the error message as above

This is the connection string:

odbc:Driver={ODBC Driver 17 for SQL Server};DATABASE=--;Server=-----------;Trusted_Connection=yes;Integrated Security=SSPI;

Thanks!!

[tothd]

Hello Vernon,

For these Errors, it looks like it may be a permission issue.

Error 2:

https://serverfault.com/questions/782680/logon-failure-the-user-has-not-been-granted-the-requested-l...

"It means what it says and says what it means. Standard users can not normally and should not normally log on to the desktop of an Active Directory Domain Controller.

You will need to create a group policy that allows users to 'log on locally' (IIRC, disables "Deny log on locally) for DCs and apply it to the user.

The better approach is to not use a DC as your desktop. If you need a DC for development, run it as a virtual guest on a standard workstation in hyperv and use the standard workstation (or another hyperv guest if you need a workstation to be a domain member) for your dev users."

For 1 and 3, also permissions I think.  Based on the credential sets not working.  If 2 goes thru and you can use a more native SQL server connection, you could put OLEDB and ODBC to rest?