SQL Server connection string with embedded credentials

Question

Hi there! We're trying to setup a dsn-less connection to SQL server for use with the Alteryx Server.

odbc:Driver={SQL Server Native Client 11.0};Server=server,port;DATABASE=dbname;Uid=username;Pwd=password;Trusted_Connection=yes;

The use case is that people without normal access to the SQL table can still run the workflow. We setup a SQL Server authenticated user and embedded into the connection string.

When people run the workflow though, their own credentials seem to override the embedded connection. Since they don't have access, the workflow fails.

Anyone have suggestions or see what we're doing wrong?

Thanks!

DaveCarver · Answer

i know this is old but the answer is that you need to specify Trusted_Connection=no

mcha54 · Answer

The person is running the workflow on Gallery.

In the "login failed for user xxx" -- it actually shows the user's credentials.

It looks as though the workflow is replacing the user's own credentials instead of using the embedded credentials in the connection string.

patrick_digan · Answer

@mcha54 Your setup seems like it should accomplish what you're after. Can you provide some more details:

1) Is the person running the workflow on designer or the gallery?

2) Can you paste the error you're receiving? either the error text or a picture would do.