Connection to external SQL Server database

David Sousa

Question

Hello,

I'm trying to set a connection to a local database running on my machine. In the Service Center I added a new connection with the following connection string:

user id=sa;password=<hidden>;data source = <ip>,1433; initial catalog = <database name>; trusted_connection=yes;

With that connection string I'm obtaining the following error:

Connection String test failed: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)

I've also tried attaching to the connection string the following, but with no success:

network = DBMSSOCN;
integrated security = no;
integrated security = yes (and using windows authentication)

Am I doing something wrong defining this connection? Any tips?

Thanks in advance,
David

12 May 2016

Carlos Henriques

Does the SQL Server instance has a name, if so you have to specify it:

data source = <ip>\<instance>

Check this site, it has some examples:
https://www.connectionstrings.com/sql-server/

Also check if the servers can communicate with each other. (firewall, etc.)

12 May 2016

David Sousa

I've tried those combinations and I'm getting the error I've shown in the first post as well as this one:

Connection String test failed: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I've tested the connection with an UDL file and there seems to be no issues.
I've allowed remote connection in SQL Server and defined the connection through the port 1433. Also, I allowed the connection in the firewall.
Is there a way of testing the server communication? (firewall, etc)

12 May 2016

Carlos Henriques

David,

This is a generic connection error, so there is one of two problems:

1. The connection string is wrong or has some typo;
or
2. Your OutSystems Server can't connect with the external SQL Server;

Are you using instance names?
- If so, check if you have SQL Server Browser service running;

Did you check the firewall in your SQL Server machine?
- Create a rule for the instance service;
- Create a rule for the browser service;

Does your instance has TCP protocol enabled?
- If not, enable it.

Are you using the IP or the name of the SQL Server?
- If your are using the name make sure OS server can resolve it or try the IP

Are the 2 servers in the same network or domain?
Can you reach the SQL server instance from your machine through Management Studio?

13 May 2016

David Sousa

Carlos,

I've tried everything you said and I think I found out the issue. I'm trying to connect through a network and the network is protected by firewall with configurations I can't change.

What I did next was to create a server using an Azure account, I allowed the IP and I connected using Service Center with success.

After that I used Integration Studio to connect to the external table but I'm getting a new error:

1.So here is my connection correctly configured in the Service Center

2. Here is the available connections in Integration Studio. I select Test Database and proceed.
.

3. And this is the error I get. I've googled this error and got no good results. Does it have something to do with SQL Server being on the cloud, on an Azure account?

Once again, thanks for the help.

David

13 May 2016

Carlos Henriques

Are you trying to integrate with Microsoft Azure SQL? If so, upgrade your OutSystems platform to the latest version 9.1.400.0:

https://www.outsystems.com/home/downloadsdetail/98/1737/

What's new in 9.1.400.0

Improved security of applications generated code (#1202532)
Added support for integration with Microsoft Azure SQL (#1202530)

13 May 2016

David Sousa

I'm afraid I'm already using the latest version of the platform.

Is this error caused by the version of SQL Azure I'm using?

I connected to the server with Microsoft SQL Server Management Studio and added some tables to the database. In that application I can't query the tables and the same error pops up:

Reference to database and/or server name in 'OST_TEST.dbo.Field' is not supported in this version of SQL Server.

13 May 2016

CasaSpider

Hello,

I have the exact same problem as David. The problem is that Azure doesn't allow you to query the master database. However, when creating the database connection in Outsystems I already provide the database I want to use. The test connection went OK.

In Integration Studio I choose the database connection and then instead of showing the database I already specified, Outsystems is trying to create a list of available databases. For this it queries the master database, which is not allowed.

Best solution is that Outsystems uses the database that is specified in the connection. Or is there another?

Best regards,

Peter Nijhuis.

17 Jul 2016

Dwight Lantz

I am also running into this issue during my eval of OutSystems?

Is there a solution or workaround?

28 Jul 2016

João Amorim

Hello David, Peter and Dwight

Can you tell me if in your ServiceCenter the DBMS dropdown in the Database Configurations has the option "SQL Server / SQL Azure"?

If you only have the "SQL Server" option, check if the Platform Server version is above 9.1.400.0. You can see this on the right side of the screen in Service Center.

As previously said, Azure is only supported after version 9.1.400.0 of the Platform Server. Currently the personal environments are running the 9.1.301.0 version which still does not support Azure.

29 Jul 2016

1 reply

01 Aug 2016

Show thread

Hide thread

CasaSpider

Hi João,

I can confirm that I do NOT see the option SQL Azure in the screen you mentioned. I only see SQL Server. My version of Service Studio is 9.1.501.0 and I'm using the Personal Environment.

Thanks for your answer, however it is still a pity that we're not able to access an Azure database... :-)

Best regards,

Peter Nijhuis.

01 Aug 2016

Alex Dellinger

Is there going to be a release of the personal environments in the near future with this support? It would certainly assist in our evaluation.

01 Aug 2016

João Santos

Hi Peter,

Running Service Studio and/or Integration Studio on 9.1.501 won't help you.

The connection to SQL Azure is a server-side feature and therefore does not depend on the version of the client-side tools.

Your Personal environment on the OutSystems cloud should now be running 9.1.301, as you can check by browsing to https://<your_environment>.outsystemscloud.com/servicecenter. Check the screenshot in Joao Amorim's reply for an example of how to check the version number.

The Personal environments are upgraded regularly, so it should be a matter of a few weeks.

Joao

16 Aug 2016

Rahul Deole

I had also same issue with personal account out system

error-Connection String test failed: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

error -26.PNG

11 Jun 2018

Rahul Deole

I had done other trouble shoots available with websites ,give mapping access to users

whats missing

11 Jun 2018

Justin James

MVP

There is no reason to think that a server in the cloud can access that SQL Server Express instance, it looks like it is on your local computer. How would it resolve DNS to it? Do you have that server available to the external world? Etc.

J.Ja

12 Jun 2018

Rahul Deole

Can you please share the connection string

12 Jun 2018

1 reply

13 Jun 2018

Show thread

Hide thread

Justin James

MVP

RAHUL DEOLE wrote:

Can you please share the connection string

How would I (or anyone else on here) know the proper connection string to your server? How could we ensure that it has the right firewall settings to allow traffic from this OutSystems environment to the database server? Etc.

You need to work with your IT department to do this.

J.Ja

13 Jun 2018

Siya

MVP

Hello Rahul,

Good Day.I could see that you are using OutSystems personal development environment and you are trying to access a server in your premise from OutSystems server. Inorder to solve your problem you need to understand a bit of infrastructure on how these systems are hosted. OutSystems server is hosted somewhere in USA and your SQL Server is located in India on your LAN( may be somewhere else ).

You need to enable port forwarding on your router ( i.e any transport coming to your router on port no 1433 should go to your local machine ). This is because no can see your local machine outside your LAN. Only the Router's external interface is visible to the world.

If you have static IP from your ISP please specify that IP address in the "Server" configuration page of OutSystems database Connection Screen.

You have a challenge if you do not have a static IP as your ISP will issue different IP's whenever you connect. You then have to change that IP in the connection page. To mitigate these issues you might use services like DynDNS or no-ip. Instead of hardcoding the IP address you may use the hostname provided by DynDNS or no-ip.

Conf.png

13 Jun 2018

1 reply

14 Jun 2018

Show thread

Hide thread

Rahul Deole

Siya EM wrote:

Hello Rahul,

If you have static IP from your ISP please specify that IP address in the "Server" configuration page of OutSystems database Connection Screen.

Thanks for solving the my problem sir

14 Jun 2018

Rahul Deole

Can anybody share sample example of connection string i will modified my code as per my requirement -it is in personal devlopment

i had written a connection string its create error -: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

TCP/IP ,UDP at port 1433 is enable

14 Jun 2018

1 reply

14 Jun 2018

Show thread

Hide thread

David Sousa

Rahul Deole wrote:

Can anybody share sample example of connection string i will modified my code as per my requirement -it is in personal devlopment

i had written a connection string its create error -: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

TCP/IP ,UDP at port 1433 is enable

Hello Rahul,

1. Since you're using a named instance, can you verify that SQL Server Browser service is running?

2. Did you try connecting using IP and port?

3. You can find a lot of connection string examples here. For example:

Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;Password=myPassword;

Cheers

14 Jun 2018

Rahul Deole

Thanks Mr. Siya EM for Solving my problem, Now sql server connection is working

14 Jun 2018

SpeedNet Limited

Rahul,

Share what you did to make OutSystem connect to your local Machine SQL Server 2017.

Ajayi Tope

23 Jul 2020

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines