Issue with Execute SQL and workflow constant for connection string

  • 11 August 2014
  • 7 replies
  • 50 views

Userlevel 4
Badge +12

Hi,

I haven't tried to use this feature before so I'm not sure that I'm doing it right or if this is an issue.

Nintex Workflow 2013 (build: 3.0.7.0)

The DB connection string is: Server=SQL01;Database=MyDBName;User Id=USERNAME;Password=MYPASSWORD;

(I am using a standard SQL user account not a windows credential)

I have confirmed that the string works. Firstly by using the connection string tool from Vadim's blog site

Secondly if I add an Execute SQL action and paste in the connection string. Then use the run now test I am able to use the test connection button successfully and also run a simple select statement correctly.

What does not work for me.

I have created two workflow constants at the farm level

1) a string with the sensitive constant button ticked

2) a secure string

Both constants have been secured to a specific group of users who are designing the workflows. (Basically my admin account for this testing).

I have pasted in the above DB connection string into the values for these constants.

When I run the test using either of these constants, the run now fails.

I receive the following error message.

The execution returned an unexpected error.

Format of the initialization string does not conform to specification starting at index 0.

Can anyone confirm the correct way to use this?

Or is this a bug?

Thanks,

Gavin


7 replies

Badge

Hi Gavin

First thing that springs to mind is the credentials you are using.

Does the account that the workflow instance is running under have permissions to connect to that DB? (in other words, you say that you are using "a standard SQL user account" to test the connection but when you run the workflow, is that using the same account or a different Windows user account?)

Userlevel 4
Badge +12

Hi Simon

it's the same connection string both ways

If I just copy the full connection string which includes the username and password into the connection field on the action it works.

If I reference a workflow constant for the connection field on the action it does not work.

The value for the workflow constants are the same as what I paste directly in.

Edit: Just to clarify as the connection string is the same for both tests and it is an SQL user identity not a windows identity the DB permissions should not be an issue. Secondly as it's an SQL identity specified in the connection string then the credential of the workflow instance should not matter either.

Userlevel 6
Badge +22

Hi Gavin,

The SQL string cannot be put into a workflow constant and then encrypted by either ticking the sensitive checkbox or selecting secure string from the drop down because the database will not be able to understand the ecryption.

I suggest ticking the Windows Authentication box if you are using windows authentication and using a sensitive Workflow constant for the database credentials.

If you are going to use the same connection string over and over you can put it into a workflow constant as a plain string minus the credentials of coarse and use the credentials in the sensitive workflow constant.

Hope this helps.

Userlevel 4
Badge +12

Hi Vino,

you have a few options.

1) I use a workflow constant of type credential. I have that workflow constant secured so that only a few users can incorporate this into their workflow design.

2) In the general settings for Nintex Workflow in central admin there is a setting:

     Allow Execute SQL action to use the application pool identity.
The default is set to no. You could enable this and then it would use that managed account to auth to SQL. Set your database permissions accordingly.

3) Go Kerberos for SharePoint and SQL. I haven't tested this one but as workflow actions execute under the credential of the workflow initiator it should pass through the user credential on the double hop through to SQL. Again set you SQL DB permissions accordingly.

Cheers,

Gavin

Badge +4

Hello!  What if my credentials are a MS SQL db account, not windows.  Is it possible to use the credentials in the credentials workflow constant? 

Thanks.

Userlevel 4
Badge +12

Yes it works in the string connection string format as per my original question.

However you cannot 'encrypt'/hide the string with the sensitive or credential constant types.

That means the username and password are in clear text.

You could limit the permissions/security on the workflow constant so that only certain designer can see the workflow constant.

Not great but might be ok depending on your environment.

Badge +1

Hi, I know this is kinda old, but it's the only thing I've found.

Is this really our only option? (perhaps there's a better one now)

There's no way to hide/obfuscate the MS SQL user password at all in the connection string (within the Execute SQL action)? Our whole DB environment works with MS SQL users and so, the DB admins, don't want to leave the password in plain text (for obvious reason).

Thx!

Reply