Can't connect to external SQL Server DB

Question

I am using SDL Web 8.5 in an AWS instance and, as part of some event system code, am trying to connect to a non-Tridion DB on the same DB server.

The connection string is: Integrated Security=true;Server=EC2AMAZ-B3R11EB;Database=Tridion_cm_poc1_Supplemental_Data

The WindowsIdentity User: NT AUTHORITY\NETWORK SERVICE

The server name is: EC2AMAZ-B3R11EB

The WindowsIdentity User: NT AUTHORITY\NETWORK SERVICE

The Environment User Domain Name: WORKGROUP

The Environment Machine Name: EC2AMAZ-B3R11EB

And yet the error that I'm getting, in the CMS, is:

Initialization of the extension
'C:\PublishingHistoryLog\EventSystem.PublishHistoryLogger.dll' failed.
Cannot open database "Tridion_cm_poc1_Supplemental_Data" requested by the login.
The login failed. Login failed for user 'WORKGROUP\EC2AMAZ-B3R11EB$'.

There is no WORKGROUP\EC2AMAZ-B3R11EB user.

What am I missing with this? Why is the current user being returned, from SQL Server, as a user that doesn't exist?

Answer 1

If you use Windows Integrated security and the “client” process runs with the special identity NT AUTHORITY\NETWORK SERVICE (or NT AUTHORITY\SYSTEM), it is represented on the network resource (SQL Server in this case) as the machine account: <Domain>\<MachineName>$. You will have to ensure this machine account has permissions to access the network resource.

See, for example: https://serverfault.com/questions/135867/how-to-grant-network-access-to-localsystem-account

BTW: please be aware that if you connect to a separate database in a TOM.NET event handler, you cause transaction promotion. That is: you enforce the use of Distributed Transactions, which means that you won’t be able to use a Cloud Database (DBaaS, such as AWS RDS).