Connecting to Azure SQL Database and Executing Stored Procedures

[brucekr]

Trying to use the database connectivity toolkit to perform what seems like a straightforward task, and keep running into a wall.  The main issue I'm having is establishing the connection.  I'm trying to use ADO so that no client side ODBC configuration is required using the control panel.  No matter how I format the 'connection information' string, it just doesn't seem to connect.  But if I enable the prompt feature that brings up the database connection configuration, I can establish the connection manually.  Clearly, this isn't a viable run-time solution.

My connection string from Azure using ADO is:

Server=tcp:nunya.database.windows.net,1433;Database=DB-Name;User ID=xx@nunya;Password=yyyyyyyy;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;

If I pull the username and password out and wire them to the corresponding inputs, I get the same error from Labview.

ADO Error: 0x80004005
Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified in NI_Database_API.lvlib:DB Tools Open Connec (String).vi->Untitled 1

Could someone offer any suggestions?

Thanks,

Bruce

[Dennis_Knutson]

I would suggest you create a UDL file and use that or copy the connection string from the file you create.

[brucekr]

Thanks for the tip!  I created the UDL and edited it using the configuration form that comes up when you double click a UDL file.  Then opened that with notepad and copied that connection string.

[michifez]

Bruce, did you the UDL file resolve this error for you? I'm experiencing a similar error.

[brucekr]

Yes, going through the wizard that pops up by double-checking the udl file created the proper string for me to use.  If you can't connect using the wizard, the connect string it generates will be useless.

The SQL configuration is all managed by our IT department and there is a ton of configuration options related to security.  So my recommendation would be to sit next to the admin of the database and make sure he has connectivity to the db via visual studio or whatever he uses.  And have them generate the connection string to assist you.

I have to be on site to even connect because of the restricted IP address range that is allowed to even connect.  You could easily be fighting something like this without knowing.

Good luck! 👍

[michifez]

Thanks Bruce. Using the LV Data link wizard to create the UDL file <connection string> solved the problem...and was glad the connection test passed. I had attempted to use the connection string obtained from the Azure SQL server config to access the database using LV database vis.

--

In case someone else attempts this for a simple database app, these are the steps I used:

1. Capture connection string from Azure server you created (make sure firewall rule is created for IP address of workstation). Also leave out the "tcp:" part in the server conn. string.

2. Go to: LV>>Tools>>Create Data Link (opens wizard)

3. Configure and test connection to server (Bruce''s point above)

4. Save the UDL file generated IF test passes. 

5. If using the Database toolkit vis, point to the path of the UDL to create a connection ref. for access to the db.

**Don't bother setting up the ODBC Data Source configuration manager.