MSSQL Execute SQL - Using Integrated Security does not work if username/password filled in

Automise Discussion
1

I have used the MSSQL Execute SQL action many times with no issue, but I think I have found a bug. Maybe two. First, I have put in a username of DOMAIN\username and put the password in the password box. When I try to run the action, I get an error:

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user ’
DOMAIN\username’…
Execute failed, return code: 1

Note the carriage return between the quote and the username, and the trailing commas/periods after the username. It looks like the username may not be getting parsed correctly.

However, the next bit is even stranger. I checked the “Use integrated security” box in the action properties, which caused the username and password to be greyed out. However, when the action ran, I got the same error related to the username. That username and password should have been ignored when “Use integrated security” was checked, no?

Removing the username and password and checking the “Use integrated security” box seems to work as designed.

2

Hi Jonathan,

You can only log in to SQL Server with a Windows Authentication user using Integrated Security, so entering ‘DOMAIN\user’ into the Username field will always result in a Login Failed error.

If you look at the SQL Server logs, it should say something like “Login failed for user ‘domain\user’. Reason: Attempting to use an NT account name with SQL Server Authentication”. You need to set up a login account as a SQL Server authentication user to use the Username/Password fields.

Regarding the extra newline, this is the output from sqlcmd. The username is sent to the sqlcmd command line by Automise as entered.

We’ve tested the “Use integrated security” option today, with and without the username filled in, and it appears to work correctly in both scenarios. Looking at the code, the logic is set to only send the username and password if the “Use integrated security” option is not ticked. Are you using the latest version of Automise?
.

3

I should have specified in my earlier post, sorry. We are using Automise 5.0.0.1580. Was there something fixed recently related to the Integrated Security logic?