0

I have successfully created the Self Hosted Integration Runtime and it is showing up and running in ADF as well.

However when I try to create a linked service to connect to SQL database on the on prem system - I am getting the below error:

Cannot connect to SQL Database: '(localdb)\mssqllocaldb', Database: 'XXXX', User: 'XXXXX'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access. Cannot open database "XXXX" requested by the login. The login failed. Login failed for user 'NT SERVICE\DIAHostService'., SqlErrorNumber=4060,Class=11,State=1..

Dale K
  • 25,246
  • 15
  • 42
  • 71
user961
  • 453
  • 6
  • 20
  • Does this answer your question? [Login failed for user 'IIS APPPOOL\ASP.NET v4.0'](https://stackoverflow.com/questions/7698286/login-failed-for-user-iis-apppool-asp-net-v4-0) – Nick.Mc Jun 01 '22 at 21:50

2 Answers2

0

The login failed. Login failed for user 'NT SERVICE\DIAHostService'.

This is not a firewall error this is an authentication error.

You have not granted that windows user account NT SERVICE\DIAHostService access to your database.

This issue is identical to this one, which has instructions on how to solve:

Login failed for user 'IIS APPPOOL\ASP.NET v4.0'

To select data from a table in SQL Server you need three things set up:

  1. Network connectivity (this is fine for you, otherwise you'd get a network error)
  2. The user account granted connect access to the server / db (this is what the error is complaining about - you need to do this)
  3. The user account granted sufficient access to select from the table (you also need to do this)
Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • This is the account with which the Self Hosted Integration Runtime is registered. So looks like we need a way to allow access to SHIR so it can reach the on prem SQL database – user961 Jun 01 '22 at 14:35
  • Follow the link above which shows you how to grant this account access to the required database – Nick.Mc Jun 01 '22 at 21:52
  • @user961 I just noticed you are connecting to `(localdb)` which has it's own complications. Is there any reason you are using `(localdb)` and not a proper SQL installation like SQL Express? Also is `(localdb)` installed on the same machine as the integration runtime? – Nick.Mc Jun 02 '22 at 08:03
  • yes, localdb and self hosted integration runtime are on the same machine. I can try using the named instance and update if it works. – user961 Jun 02 '22 at 08:17
-1

Please do the below :

Allow outbound TCP communication on port 1433 for both the Windows firewall [ Windows-firewall-setup ] and the corporate firewall [ Ports-and-firewalls ]

Configure the firewall settings of the SQL Database to add the IP address of the self-hosted integration runtime machine to the list of allowed IP addresses.

SHIR setup reference

Ashwin Mohan
  • 108
  • 9
  • thanks for your response. I think these are the steps that are missing in my setup. Can you please share the steps to Configure the firewall settings of the SQL Database to whitelist the IP address of the self-hosted integration runtime machine to the list of allowed IP addresses. – user961 Jun 01 '22 at 05:07
  • It's not a firewall error it's an authentication error. Also an on prem server doesn't have a database level firewall. – Nick.Mc Jun 01 '22 at 08:11
  • @user961, actually could you check and test if the [remote-connection](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/view-or-configure-remote-server-connection-options-sql-server?view=sql-server-ver16#to-configure-remote-server-connection-options) is enabled ? – Ashwin Mohan Jun 01 '22 at 08:20
  • @Nick.McDermaid , if that's the case, then user961 can do a 'test connection' from the Diagnostics tab of the Microsoft Integration Runtime Configuration Manager to confirm. – Ashwin Mohan Jun 01 '22 at 09:01
  • 1
    I have tried connecting to this database using Self Hosted Integration Runtime native UI and getting the same error there as well. so what should I be trying next? – user961 Jun 01 '22 at 10:52
  • @AshwinMohan - the remote connection in the database is also enabled. – user961 Jun 01 '22 at 10:56
  • 1
    You need to grant the user access as per my answer. I will add details if you wish to pursue this. – Nick.Mc Jun 01 '22 at 11:21
  • @Nick.McDermaid - somewhere I feel that the integration runtime is not able to access this on prem SQL server because of which this issue is coming up. The account that I am using to access this SQL database is able to login successfully using SSMS. If you can suggest me something on enabling the access for SHIR, It will be great. and yes I am up for trying any possible recommendation to make it work and find the actual root cause, so please do share. – user961 Jun 01 '22 at 14:30
  • _The account that I am using to access this SQL database is able to login successfully using SSMS._ no you are using a different account to connect in SSMS. You aren't using `NT SERVICE\DIAHostService` you are using something else – Nick.Mc Jun 01 '22 at 21:53
  • @Nick.McDermaid - I have followed the steps to grant the windows user account NT SERVICE\DIAHostService access to the database, given it db_owner permission in the database and still getting the exact same error. appreciate your help in this issue. – user961 Jun 02 '22 at 05:44
  • Please don't comment on this answer, comment on the answer below – Nick.Mc Jun 02 '22 at 06:05