macleary
Design Dabbler

Connection string format for connecting to a SQL Server db that resides within a VM

I'm trying to figure out the connection string format for connecting to a SQL Server db that resides within a VM. Not a SQL Azure db. The VM is out in Azure, but it is not a SQL Azure db.

 

(This post however is very descriptive in describing what is needed for SQL Azure: https://community.nintex.com/t5/Community-Blogs/Creating-Microsoft-SQL-Server-Connection-to-SQL-Azur... )

 

Normally, within my network in SQL Mgmt Studio I simply use ServerName\Instance such as DAVE2019\DEV1. This format used to work when I used Nintex on prem within SharePoint, but that doesn't work in Nintex Workflow Cloud, I get "This field has an invalid format".

 

I am getting here by choosing "Connections" menu, Add new: Connector: Microsoft SQL Server. The "Configure a Connection" popup box appears. I fill in the 5 fields but the "Database Host" field is where I'm getting the invalid format error, (in red just underneath the text box I type into).

 

I followed the instructions in https://help.nintex.com/en-US/nwc/Content/Designer/Connectors.htm#MicrosoftSQLServer and we added all of the Source IP addresses (18 of them) in the US region into our Azure Portal firewall as well I added them onto the VM's firewall.

 

Thank you for your help!

Tags (1)
0 Kudos
Reply
2 Replies
Gavin-Adams
Process Pupil

Re: Connection string format for connecting to a SQL Server db that resides within a VM

Hi @macleary ,

Normally the database host will be something like 'sql-server.company.com'.

ie a full qualified domain name.

 

You will need this to be in the external DNS for your company's domain (or what ever domain you are using). Typically this will be a host entry point to an IP address on your firewall with some kind of NAT rule on the firewall to direct the traffic to your internal SQL server. Plus you've already covered off the only allow from the whitelisted IP addresses.

 

I've not played with Azure portal firewall before so I'm not sure how it handles external hostnames or IP addresses.

 

Taking a quick look at the azure firewall doco, check out this reference

Configure Azure Firewall application rules with SQL FQDNs | Microsoft Docs

Your scenario would be similar to 'From on-premises to Azure SQL Managed Instances or SQL IaaS running in your VNets.'

 

The last point is that the account in the username needs to be a SQL user login not a windows domain account that you have added into SQL security.

0 Kudos
Reply
macleary
Design Dabbler

Re: Connection string format for connecting to a SQL Server db that resides within a VM

Hello. Thank you for your reply. I still didn't get this database connection string setup. Is there anyone else with a sure fired way on how to form the string? Or, is there a way for me to open a paid support ticket w/a Nintex tech to help our company get over this one stumbling block? The company I work for is also a Nintex partner so maybe that could get us some technical support?
0 Kudos
Reply