Connect to other database in LAN

Question

1.00/5 (1 vote)

See more:

Hi
we are facing issue on connecting database on another machine on LAN. the code connection works fine while we run in from vs project and it make effect on other pc database, but throw error when we host it on iis on local machine. we try many settings but no success until now.

sql code sample

SQL

update OPENDATASOURCE('SQLN211','Data Source=ABC;User ID=sa;Password=ABC;Integrated Security=SSPI').ServerName.dbo.EmpAtt

error

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

What I have tried:

some firewall settings, SQL configuration TCP settings

Posted 15-Mar-21 22:33pm

Medo-I

Updated 15-Mar-21 23:02pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2021-03-15T22:48:00

The problem is not in your SQL code; it is in your application code, which is connecting using Windows authentication.

When you run the project in Visual Studio, it runs under your user account. The connection to SQL uses your Windows account, which is configured to allow access.

When you run the project in IIS, it runs as a local account with no network access. When it tries to connect to SQL, it is using the "anonymous logon" account, which does not have access to SQL.

Either change your connection to use SQL authentication, or change your application pool to run as a domain user with permission to access SQL.

NB: Using the sa account to connect to SQL is almost always the wrong thing to do. This is an unrestricted account which could be used to compromise your entire server. You should almost always connect using a specific account which has only the permissions required by your application.

And if that's your real sa password, change it immediately! And make sure you change it to something secure.

Maciej Los · Answer 2 · 2021-03-15T23:02:00

Richard Deeming is right! Please, see:

MSDN wrote:

Important

Windows Authentication is much more secure than SQL Server Authentication. You should use Windows Authentication whenever possible. OPENDATASOURCE should not be used with explicit passwords in the connection string.

Source: OPENDATASOURCE (Transact-SQL) - SQL Server | Microsoft Docs[^]

BTW: if you want to connect to the other database in LAN, the option is to use linked server. See:
Create Linked Servers - SQL Server | Microsoft Docs[^]
How to create and configure a linked server in SQL Server Management Studio[^]

Then you'll be able to easy get data form it ;)

Good luck!

Connect to other database in LAN

2 solutions

Solution 1

Solution 2

Important

Add your solution here

Preview 0