Can BarTender connect to an Azure SQL database?
Question
Can BarTender connect to an Azure SQL database?
Answer
The answer to how to achieve this would depend on whether the connection needs to be made directly from BarTender or if a third party is connected to the Azure SQL database. In either case, BarTender needs to be integrated with the third-party system.
Options to connect to the third party system
If integrating with the third party system, it might be possible to use one of our integration methods available, such as:
- BarTender Integration Builder
- BarTender .NET SDK
- BarTender XML (BTXML)
You can find further information on our Integration Platform in the following technical document:
Integrating with BarTender Integration Builder
Options to connect directly to an Azure SQL database:
By using the ODBC Driver for SQL Server
Currently, it is the Microsoft ODBC 17 driver for SQL Server that can be downloaded from the Azure website in the "Connection strings" options and that can also be downloaded from here:
https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-2017
Then in BarTender, we just need to choose to create an ODBC database connection and select the option "ODBC Administrator" to "Add" the new "ODBC Driver 17 for SQL Server":
Then following the database wizard ("File > Database connection setup" in BarTender), we can define the connection to the server but it is important, in the wizard, to make sure that:
a. The SQL authentication is selected:
b. That we are connecting to the right database (instead of to the default one):
Finishing the wizard allows us to successfully connect to the Azure database.
By manually creating a Named Database connection
a. In the Azure connection strings, we click on the ADO.NET and copy the provided code:
b. Then created an XML file (AzureConnection.xml, for example) with the following syntax:
b1. The first and last tags should be <DatabaseConnection> and </DatabaseConnection> respectively
b2. In between those tags, we should paste the ADO.NET connection string
b3. In the ADO.NET string, we change the "Server=" to "Data Source="
b4. We need to specify the user and password
The result is something like this:
<DatabaseConnection Type="SqlServer">
Data Source=tcp:servername,1433;Initial Catalog=AzureTest;Persist Security Info=False;User ID=user;Password=password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;
</DatabaseConnection>
Then we copy the file in the path where BarTender stores the settings for the Named Database connections:
C:\ProgramData\Seagull\Database Connections
Afterwards, we should be able to connect to our database via the "File > Database connection setup" option in BarTender