DSN-Less SQL Server Connection

[lwolfie]

I'm trying to get a database connection to work on server without having IT setup the database connection AND without the Server Account having access to the database.  From what I've read I am trying to use a DSN-Less connection.  The connection works on desktop.  I've tried many routes and many different combinations of the following steps, but basically it's:

1. Create the DSN-Less Connection.

2. Synchronize with Server.

3. Create my workflow with said connection. 

4. Save the workflow to Server.

5. Run the workflow.

Upon running the workflow, I get an error that it can't find the connection.  If my IT Server Admin, goes onto the server and creates the DSN-Less connection through File-Manage Connections and creates it as a System connection, then it works.  The problem here is that IT is involved.  

I have verified the same drivers are on the server.  I've also been on the phone with Alteryx Support and they haven't been able to come up with a solution.  

Is the only way for database connections to work, is to have IT set it up on that side?  

[gawa]

@lwolfie A couple of thoughts that come to my mind:

1) Driver Name

As DSN-less connection use connection string including driver name, if the version of drivers differs btw client and the Server, it will not work.

For example...

Designer:  ODBC Driver 17 for SQL Server
Server:  ODBC Driver 18 for SQL Server

If this is applicable, get the drivers from the product download portal and install them  on both the client and the Server.

2) DCM is surely synced with the Server or not

To check DCM sync mode of your Server. If it's "One-way to Designer", your created DCM at Designer won't be synced with Server.(DCM created on the Server will be synced to Designer).

Your IT admin might have created DCM on the gallery menu of the Server, not on the Designer on the Server.

I wrote the above statement, assuming that you use DCM. If it's not the case, please forget about it.

[lwolfie]

We have verified the Driver is the ODBC Driver 17 for SQL Server.  We are using DCM.  The server is also set to full synchronization.  It's also set to DCM Optional and SDK AllowAll.

[apathetichell]

Might be an unpopular opinion --- but I think DCM has created as many problems as it's solved.

@lwolfie can you provide the exact error message? If both workflow and server/worker are set up for DCM and DCM is running off a DSN-less setting --- well then. um. maybe it's something else?

[jrgo]

@lwolfie Are you creating connections to use the In-DB tools or the standard Input/Output tools?

[lwolfie]

I'm using In-DB Tools.

The exact error is not helpful.  "Error finding connection "SesameStreetTest"".  I am using DCM on both desktop and server.  

[jrgo · Accepted solution]

I suspected that was the case. Long story short, when creating your In-DB connection, you need to create it as a file as opposed to a user or system (if running as admin) connection. When you publish your workflow, you'll want to include that .indbc file you created as an asset.

Reason, In-DB connections, while compatible with DCM, store the connection details in a separate config file. The ID that's references the DCM connection is stored in that file, which - if you created a user or system connection - only exists on your own machine, not the server. Creating and including the .indbc file with your workflow will provide Server/Gallery with what it needs for establishing your connection and avoid your IT from creating the connection on the server directly.

Hope this helps!

[lwolfie]

Is it just creating the connection string?  So if we use Windows Authentication I don't have to worry about credentials being stored in the file.

[jrgo]

No, the connection file is not just creating the connection string. It contains the instruction that the In-DB tools will use for establishing the connection.

If the goal is to avoid IT having to create connections on the server, it doesn't matter. DCM makes it more of a requirement to use a connection file, while using a non-DCM connection is not, but will require someone to create "System" In-DB connections on the server itself. Using an In-DB connection file resolves both scenarios.