Sql-server – SQL Server Express 2012 SP1 let me log in, but shouldn’t have

I connected to an instance of SQL Server Express 2012 SP1 on another machine via my workstation's SQL Server 2014 Management Studio. The instance has mixed mode authentication and I chose to log in with Windows Authentication using my domain credentials. While connected I did the things I needed to do (including deleting a database), and everything seemed fine.

Then I tried to publish a dacpac to the instance using Integrated Security in the connection string, which failed with the error "You must have a user with the same password in master or target server".

I then tried to RDP to the SQL Server machine to take a look, using my domain credentials, but that failed because my credentials "did not work". That surprised me, but I know a local user account for the machine and I was able to RDP into it with the local account.

Then I looked at the system properties and discovered that the machine is not actually joined to the domain like I assumed. It's a member of the WORKGROUP workgroup.

I looked for anything related to my domain or my user in all of the Users and Groups in the machine's lusrmgr.msc (Local Users and Groups), but found nothing. And no login related to my domain or user for the SQL Server or any of its databases. I also checked that my workstation doesn't have any Windows Credentials set up for this SQL Server.

To get more information, I attempted to start a SQL Server Profiler session, using Windows Authentication and my same domain credentials, but profiler said: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (pfutil)

I also RDPd to another machine on the domain, and attempted to connect to the SQL Server from that machine's SQL Server 2012 Management Studio, using my same domain credentials, but it said: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)

The "untrusted domain" errors are what I expect. I feel like I should receive that same error from my workstation's SSMS. In fact, if I look at the SQL Server Log File Viewer after I log in, or after I run a query, I see this…

Date 5/24/2017 9:12:02 PM Log SQL Server (Current – 5/24/2017
9:11:00 PM)

Source Logon

Message Error: 17806, Severity: 20, State: 14.

Followed by this…

Date 5/24/2017 9:12:02 PM Log SQL Server (Current – 5/24/2017
9:11:00 PM)

Source Logon

Message SSPI handshake failed with error code 0x8009030c, state 14
while establishing a connection with integrated security; the
connection has been closed. Reason: AcceptSecurityContext failed. The
Windows error code indicates the cause of failure. The logon attempt
failed [CLIENT: ]

Yet, my connection was not closed and I seem to have admin-level privileges. How is this possible? It's got me worried.

The machine is running Windows Server 2012 R2, and it looks like it hasn't been patched in a while, so I'm running Windows updates now and hoping that this is due to a bug that has already been fixed.

EDIT: I ran Windows updates, but afterwards I'm still able to log into SQL Server.

EDIT: I ran some packet captures, and I noticed that the first thing my workstation does is ask for the name of a named pipe via UDP. SQL Server returns the named pipe name and my client uses that named pipe for its queries. The other machine which can't connect to SQL Server doesn't ask for a named pipe, it tries to connect through TCP, and the logon request fails. I disabled named pipes in Sql Server Configuration Manager, and afterwards my workstation client can no longer connect, even if I reenable named pipes; seems like my client has switched to trying TCP first. The pipe name is "\.\pipe\MSSQL$SQLEXPRESS\sql\query" and I notice a login in SQL Server named "NT Service\MSSQL$SQLEXPRESS", which has the sysadmin role. So I think this explains the behavior I was seeing; the SQL Server was configured to allow highly-privileged access over named pipes. I have disabled named pipes to force clients to authenticate over TCP.

EDIT: I also just discovered that I can tell my SSMS client which network protocol to use when logging in, by selecting "Options >>", then the "Connection Properties" tab, and then the "Network protocol" drop-down will have Named Pipes as an option, as well as TCP/IP, Shared Memory, and default. So it was originally using Named Pipes, but from now on I will use TCP/IP for my remote connections to this server.