How server assigns user to specific connection

Question

I will describe the situation as we have a bit complex topic here.

With our admin we have setup a Microsoft ODB (OleDB) SQL connection on Alteryx Server using generic ID in MS SQL server (access to one schema only). My user id was assigned to that connection on alteryx server level as well.

Later I created an in-db workflow that accesses same server but different schema. Credentials for that workflow are using my user id (MS SQL) and connect to the same server. My account has more schema assigned.

How is it possible that my workflow uploaded successfully and ran on the server?

How Alteryx Server recognizes my workflow connection with its connection? Server used in both is the same but connection names, user ids are different.

scottj · Answer

I would certainly suggest using the Data Connection sharing in the Gallery.  That should alleviate any permissions issues by using your same credentials on the Server and Designer, while still securing who has access to it.

See the help documentation on Data Connections, and this Knowledge Base.

marcin2x4 · Answer

The connection on Alteryx Server is using a generic user whose password is hard-coded in the credentials. That user has access o only one schema in the MS SQL Server.

My workflow (using my MS SQL credentials with much more schema access privileges) is assigned to above connection, loads and runs successfully.

After speaking with my admin it seems that Alteryx Server checks couple things:

1. is user assigned to the connection on server side.

2. checks if users' and generic user credentials use same server name

3. pick user's privileges and overwrites the ones of generic user (used to establish connection on server side)

scottj · Answer

When setting up the OleDB connection, was a UID/PW hard coded in the driver or set up with Windows Auth?

I suggest checking the driver settings on both your machine and the server.