Data Source - Microsoft Direct SQL Agent logging into wrong instance?

replied on July 8, 2021

Laserfiche Cloud does not allow direct SQL access. What exactly are you doing?

0 0

View 6 previous replies

replied on July 8, 2021 • Show version history

It has a SQL Direct option in the Data Sources of all our customer cloud accounts. I went to Process Auto - Integrations. I created a Remote Agent and installed on a local server.

Then I went to Data Sources and created a SQL Direct type Data Source.

This Data Source is what appears to be connecting to the wrong instance given the testing described above.

0 0

replied on July 8, 2021

(probably best to go through a support case so we can see the actual settings)

What are you entering for the server name in the next screen? Is it <Machine>\<instance>?

What value are you using for the port?

0 0

replied on July 8, 2021

We are entering the servername\instance name but the instance it appears to be working with is the default instance. Otherwise how is it that re-creating the same user in the default instance lets us in and then deactivating us shuts us back out?

We are using 1433 for the port

0 0

replied on July 21, 2021

1433 is the port of default sql server instance, probably that is why it's trying to connect to the default instance. Can you please leave the port number empty and just set the server as servername\instancename, this way your credentials should work on the correct instance.

0 0

replied on July 22, 2021

Both instances are listening on port 1433, the port you come in on does not determine which instance you connect to. The instance must be set by connecting with a \instancename or just server name for the default instance.

You can test this out using SQL Studio to see what I mean

0 0

replied on July 22, 2021

They are not. SQL assigns dynamic ports to named instances when their service starts.

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port?view=sql-server-ver15

0 0

replied on July 22, 2021

If I remember right, they overrode that blank box in the network configuration with the number 1433 to force port 1433 and we checked the configuration of their other software configured for an integration to find the connection profile was also using 1433 to connect to the same instance. I am working on getting some screenshots or a gif video capture.

0 0

replied on July 22, 2021 • Show version history

Hi Chad,

I did some research and per this article:

"Each SQL Server instance must listen on a different TCP endpoint, but this does not mean that each instance has to listen on a different port: a TCP endpoint is made of an IP address and a port. This means that two instances can listen on the same port, as long as the IP addresses are different."

If two SQL Server instances on the same machine have the same IP:Port combo, whichever one's service happens to start up first will grab the TCP listener.

Please verify that each SQL instance has a different IP, that each IP has an associated different DNS entry, and that you're using the IP or DNS name for the specific instance you're trying to connect to.

0 0

replied on July 22, 2021

Oh ok, well we only have 1 IP I believe, but I remember the primary instance listening on port 1434. I will double check all this again next time I get access to the SQL server.

I know that after trying everything, all listening ports, empty port, we left the configuration exactly as it was in their working connection from their other software, which was port 1433.

0 0

Question

Question

Data Source - Microsoft Direct SQL Agent logging into wrong instance?

Replies

Sign in to reply to this post.