Connection String to SQL database

[EllenPeereboom]

​Hi all,

Since I'm very new to BluePrism and automation in general, I could use the advise of this community.
Currently I am trying to connect to an SQL database via the VBO Object 'Data OLEDB'. Now I have this connection string, but when running the object it keeps returning errors.

Connectionstring = "Provider=SQLOLEDB.1;Data Source=xxxxx.database.windows.net,1433;Initial Catalog=BluePrism;Persist Security Info=False;User ID=xxxxxxx@xxxxxx;Password=xxxxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False"

Apparently there is wide variety in connection strings and I have tried a great number of them, but so far without the right outcome. I would appreciate your thoughts on this matter.

Thanks in advance!

Ellen

[VivekGoel]

What is the error that you get? Based on that we can advise

------------------------------
Vivek Goel
RPA Architect
Asia/Singapore
https://www.rpatools.com/
"If you like this post, please press the "Recommend" Button.
------------------------------

[EllenPeereboom]

Hi Vivek,

Thanks for your reply. To provide you with a little more context: our machines and databse run in the Azure Cloud. What we are trying to do is set a connection from a Runtime Resource VM to the Azure database that has also been setup as the BluePrism database during installation. We are trying to log-in with the same service account details. The type of authentication is therefore Active Directory with integrated password, which is included in the connection string as well.

The difficulty is that when we are logging in on the Runtime Resource VM and launch SSMS 2017 with the service account details and name of the database, it works fine. We can connect and write queries and stuff. See screenshot:

Yet, forming the connection string does not give us the same result and returns the following error when we try to connect through BluePrism application:

As stated previously there is a multitude of different options when forming the string, hopefully you can shed some light on that. For instance we are not sure whether the name of the Azure database should be entered after "Server=" or "Data Source=" .. We are also not sure what the driver/provider should be.

This is the current string": 

Provider=SQLOLEDB.1;Integrated Security=SSPI;Trusted_Connection=Yes;Persist Security Info=False;Initial Catalog=BluePrism; Data Source=std-sql-a06.database.windows.net,1433; User ID=xxxxxxxxxxx@xxxxxxxxxxx; Password=xxxxxxxxxxxx;Authentication="Active Directory Password"

Do you have experience in connecting to an azure database using the service account through blueprism? Hope you can help!

Best,
Ellen

------------------------------
Ellen Peereboom
------------------------------

[KrishnaA]

Hi Ellen,

You should be able to retrieve the connection string by following instructions from this link - https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connect-query-dotnet-core

Attached contains these instructions.

Best regards,
Krishna

------------------------------
Krishna A
Blue Prism
------------------------------

[EllenPeereboom]

Hi Krishna,

Thanks for your reply.
Setting up the connection is no problem, and works with the connection string you provided. However, connecting through BluePrism does not work and yields this error:

When "Provider=SQLOLEDB;" is added to the string, the login fails again. The problem may lie only with the provider, or perhaps I need a completely different connection string.

Would you care to share your suggestions? Thanks again!

Best regards,

Ellen

------------------------------
Ellen Peereboom
------------------------------

[KrishnaA]

Hi Ellen,

May I know your reasons for using OLEDB object over native SQL VBO object?

https://docs.microsoft.com/en-us/sql/connect/oledb/when-to-use-oledb-driver-for-sql-server?view=sql-server-ver15

If you are not aware of this object, suggest you explore this VBO (available out of the box) to connect to SQL database

BPA Object - Data - SQL Server.xml

What version of Blue Prism are you using?

Best regards,
Krishna

------------------------------
Krishna A
Blue Prism
------------------------------

[EllenPeereboom]

Hi Krishna,

Thank you for the idea, my assumption that OLEDB was required turned out to be false. Using the SQL Server Object did the trick!

Best,

Ellen

------------------------------
Ellen Peereboom
------------------------------