Execute query to SQL database with Windows vs SQL Server authentication
Robot_1 is installed on the same machine as the SQL Server.
Robot_2 is installed on a separate machine.
A process that runs through Robot_1 is executing an SQL query to the SQL Server database, using ConnectionString_1
A process that runs through Robot_2 is executing an SQL query to the SQL Server database, using ConnectionString_1
In both scenarios, the robot is accessing the database with the following connection string:
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;UserID=%SQLCredential.UserName%; Password=%SQLCredential.Password%;Initial Catalog=master;Data Source=SQLHOST2
If Persist Security is 'false', the robot will access the database with Windows Authentication.
In this case, scenario #1 will work great, but scenario #2 will give us the below error:
Why is this happening?
In scenario #1, the robot is already logged in to Windows with Windows Authentication, so it will be able to access the database successfully.
However, in scenario #2, we would need to access the database with SQL Server authentication instead.
To address this, when SQL Server authentication is needed, the Persist Security Info field should be set to 'true' in the connection string.
For additional information on authentication in SQL Server, refer to this Authentication in SQL Server documentation article.