topic RE: Connection String to SQL database in Product Forum

Connection String to SQL database

EllenPeereboom — Fri, 13 Dec 2019 16:12:00 GMT

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

RE: Connection String to SQL database

vivek.kumar.goel — Mon, 16 Dec 2019 03:21:00 GMT

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.
------------------------------

RE: Connection String to SQL database

EllenPeereboom — Tue, 17 Dec 2019 11:42:00 GMT

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
------------------------------

RE: Connection String to SQL database

KrishnaA — Wed, 18 Dec 2019 08:05:00 GMT

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
------------------------------

RE: Connection String to SQL database

EllenPeereboom — Wed, 18 Dec 2019 09:02:00 GMT

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
------------------------------

RE: Connection String to SQL database

KrishnaA — Wed, 18 Dec 2019 10:49:00 GMT

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
------------------------------

RE: Connection String to SQL database

EllenPeereboom — Wed, 18 Dec 2019 15:47:00 GMT

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
------------------------------