In the process of expanding my home lab, I ran into an issue as I was setting up a Microsoft SQL Server instance in my network. The installation breezed through without a hitch, and every service started fine, and I was able to connect without issues with SQL Server Management Studio.
However, as with any new deployment of a new set of services, I always monitor and keep tabs on the system logs for any potential issues. This is where I noticed the following error in my Event Log under Application
Login failed for user 'CONTOSO\SQL01$'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
Looking further into the details of the error, I saw the following:
Log Name: Application
Source: MSSQLSERVER
Event ID: 18456
Level: Information
User: NT SERVICE\SSISScaleOutMaster160
OpCode: Info
Task Category: Logon
Keywords: Classic, Audit Failure
Computer: sql01.contoso.com
Now the first thing that struck me, was that the login MSSQLSERVER was trying to use, was the machine account SQL01 under the domain CONTOSO. In itself not curious, however, none of the services were running under a domain account, nor were any jobs running with this account.
I performed a Google Search as my own investigations turned up empty, and I quickly saw many other people having run into the same or similar issue, however, none of the proposed solutions and/or fixes applied to my situation.
At this point, it was back to the drawing board to retrace my steps. I knew that it was a login failure towards the newly deployed SQL Server instance, and it was attempting to use the machine account for the SQL server, which did not exist as a login under Security --> Logins in the SQL Server. But then I noticed that the user attempting the login request was made by NT SERVICE\SSISScaleOutMaster160.
This account is associated with the Scale in/Scale Out feature of SQL Server, which I did install. However, the feature was not fully set up by the SQL Server installer, and not having set this up before, I did some more digging.
Turned out it was quite easy. Using SSMS (SQL Server Management Studio) I connected to the SQL instance with a user with administrative privileges with the type Database Engine and then scrolled down to Integration Services Catalogs which was empty.
I then right-clicked the item and chose Create Catalog...
Here we tick the checkbox for Enable automatic execution of Integration Services stored procedure at SQL Server startup. and I created a strong password which I entered in the password fields (ensure to keep a safe note of this!). I then also checked the Enable this server as SSIS scale out master and pressed OK.
SQL Server then proceeds to create the new catalog and all its prerequisites, such as the SSISDB database.
I then monitored the event log, in this case the Security logs, and noticed the two following event IDs with ID 4799 (Security Group Management).
A security-enabled local group membership was enumerated. Subject: Security ID: SYSTEM Account Name: SQL01$ Account Domain: CONTOSO Logon ID: 0x3E7 Group: Security ID: BUILTIN\Administrators Group Name: Administrators Group Domain: Builtin Process Information: Process ID: 0x26bc Process Name: C:\Windows\System32\svchost.exe
Followed by:
A security-enabled local group membership was enumerated. Subject: Security ID: SYSTEM Account Name: SQL01$ Account Domain: CONTOSO Logon ID: 0x3E7 Group: Security ID: BUILTIN\Backup Operators Group Name: Backup Operators Group Domain: Builtin Process Information: Process ID: 0x26bc Process Name: C:\Windows\System32\svchost.exe
Which was then followed by the event ID 4624 (Logon):
An account was successfully logged on.
Subject:
Security ID: SYSTEM
Account Name: SQL01$
Account Domain: CONTOSO
Logon ID: 0x3E7
Logon Information:
Logon Type: 5
Restricted Admin Mode: -
Virtual Account: No
Elevated Token: Yes
Impersonation Level: Impersonation
New Logon:
Security ID: SYSTEM
Account Name: SYSTEM
Account Domain: NT AUTHORITY
Logon ID: 0x3E7
Linked Logon ID: 0x0
Network Account Name: -
Network Account Domain: -
Logon GUID: {00000000-0000-0000-0000-000000000000}
Process Information:
Process ID: 0x2f0
Process Name: C:\Windows\System32\services.exe
Network Information:
Workstation Name: -
Source Network Address: -
Source Port: -
Detailed Authentication Information:
Logon Process: Advapi
Authentication Package: Negotiate
Transited Services: -
Package Name (NTLM only): -
Key Length: 0
I then went back to monitor the Application log that was previously filled with the Event ID 18456 with a failed login for CONTOSO\SQL01$ and saw that it had now completely vanished, after having appeared every 30 to 90 seconds.
So the error stemmed from the fact that the Scale In/Scale Out feature was installed, but not fully configured. Once the catalog was added and the prerequisites installed, the feature worked as intended.