Microsoft SQL Server, often referred to as MS SQL Server is a relational database management system (RDBMS) developed by Microsoft that allows you to perform different actions like storing and retrieving data to and from the databases.
Authentication Credentials: Obtain the required credentials to connect to your MS SQL instance. You will need the following credentials:
- MS SQL database username and password.
- Hostname or IP address of the MS SQL server.
- Port number for database access.
- Database name within the MS SQL server.
- Configure firewall settings to allow DronaHQ (IP whitelist) access to your MS SQL server.
Ensuring these prerequisites are met will ensure a seamless integration of MS SQL with DronaHQ, facilitating effective data management and utilization.
Connector Setup in DronaHQ
Configure your database category by providing the details below. Validate the connection using
Test connection and
Save settings for a secure and efficient database setup.
DronaHQ can Auto fill crucial connection values like host, name, password, and more from the AWS IAM credentials using AWS Import.
|Host||Enter the Host Domain or IP|
|Port||Enter the Port number|
|Database Name||Enter the Database name|
|Username||Enter the Username|
|Password||Enter the Password|
Microsoft SQL – TDS Version
When configuring the Microsoft SQL connector in DronaHQ Studio, users can choose the Tabular Data Stream (TDS) protocol version. TDS facilitates communication between clients and database servers. DronaHQ Studio provides multiple TDS versions for compatibility, allowing users to select as needed.
SQL Server Proxy Host
In DronaHQ Studio's Microsoft SQL connector setup, the SQL server proxy host can be specified. This feature establishes a secure gateway between the client and the target SQL server, enabling access to external SQL resources using distinct credentials. It offers controlled execution of Windows shell commands and SQL Server Agent jobs to enhance security and operational control.
|Connection Options||The |
|TDS Version||Choose the Tabular Data Stream (TDS) protocol version for communication with the MS SQL server.|
|SQL Server Proxy Host||Specify the proxy host to route the MS SQL connection through a proxy server, if necessary.|
|Use Dynamic Database Host||Dynamically determine the host for the database connection.|
|Use Dynamic Database Name||Set the database name dynamically, allowing easy switching between databases.|
|Use Dynamic Database Credentials||Enable dynamic database credentials for secure and convenient management.|
|Enable Converting Queries to Prepared Statement||Opt for prepared statements to enhance database performance and security.|
|Connect using SSL||Securely access the database using an encrypted SSL connection, ensuring data integrity and confidentiality.|
|Enable SSH Tunneling||Securely access the database through encrypted SSH tunneling.|
|Whitelist IP||Enhance security by restricting database access to specific whitelisted IP addresses.|
Adding Database Queries
After configuring the connector, access it in your Connector Library.
Add query once the connection is established. Create queries, run them, and view responses.
Use double curly brackets for dynamic variables. Test with sample values before saving. Link dynamic variables to controls/actions for runtime use.
Saved queries appear under your connector in Connector Library.
Supported Query Operations
|Raw SQL Query||Execute various SQL operations directly, e.g., |
|Stored Procedure Call||Call stored procedures, e.g., |
Using Microsoft SQL Connector
Data Bind using Data Query
Read/Display Data Query for Microsoft SQL:
Start by constructing an SQL query to retrieve data from your Microsoft SQL database. For instance, let's consider an example where you want to retrieve all data from the
auth_permission table. The SQL query is:
SELECT * FROM auth_permission;
This SQL query retrieves all rows and columns from the
Integrate the fetched data into your application's interface.
a. Access the Controls section and introduce the Table grid control.
b. Navigate to
Data Bind Options -> Quick Select -> Database Queries.
c. Opt for the Microsoft SQL connector and choose the query that aligns with your data presentation goals.