Sql-server – Only local administrators can connect remotely

kerberosloginsspnsql-server-express

I'm facing a problem that when I try to connect to the SQL Express instance from client to server. The Server logs an error:

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: 192.168.245.50]

The account I log on with is sysadmin and have all the possible permissions on the database its trying to connect to but still it fails. But if I add the user that is trying to connect to the local administrators group it works. This is not a good solution, I don't want all users to be local administrators on the server. Why is it that the user needs to be local admin?

And I think the SPN is correctly set.

enter image description here

Its all in same domain and using domain accounts.
Windows Autentication is used for login.
Shouldnt be problem with the connectivity to the DC.
Named instances name is used (it works if the domain user is local admin).

Log from SQL error log:

03/09/2015 15:22:47,Logon,Unknown,Login failed. The login is from an
untrusted domain and cannot be used with Windows authentication.
[CLIENT: 192.168.245.50] 03/09/2015 15:22:47,Logon,Unknown,Error:
18452 Severity: 14 State: 1. 03/09/2015
15:22:47,Logon,Unknown,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: 192.168.245.50]
03/09/2015 15:22:47,Logon,Unknown,Error: 17806 Severity: 20
State: 14.

Log from the client trying to connect:

LSRetailPosis.POS.Program: System.Data.SqlClient.SqlException
(0x80131904): Login failed. The login is from an untrusted domain and
cannot be used with Windows authentication. at
System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection
owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean
allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions
userOptions, DbConnectionInternal& connection) at
System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection
owningObject, TaskCompletionSource1 retry, DbConnectionOptions
userOptions, DbConnectionInternal& connection) at
System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection
owningConnection, TaskCompletionSource
1 retry, DbConnectionOptions
userOptions, DbConnectionInternal oldConnection, DbConnectionInternal&
connection) at
System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection
outerConnection, DbConnectionFactory connectionFactory,
TaskCompletionSource1 retry, DbConnectionOptions userOptions) at
System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory,
TaskCompletionSource
1 retry, DbConnectionOptions userOptions) at
System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1
retry) at
System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource
1
retry) at System.Data.SqlClient.SqlConnection.Open() at
LSRetailPosis.Settings.Database.CheckDatabaseExistence() at
LSRetailPosis.Settings.Database.CheckDatabaseSettings() at
LSRetailPosis.Settings.ApplicationSettings.InitializeSettings(String[]
args) at LSRetailPosis.POS.Program.InitializeAndLoadPOS(String[]
args, Boolean owned, SplashScreenForm splashScreenForm) at
LSRetailPosis.POS.Program.Main(String[] args)
ClientConnectionId:d57c7fd9-4a47-41b7-af07-ed93627d2930.

Best Answer

I found a solution to this problem.

I added the domain user to: Local Security Policy -> Local Policies -> User Rights Assignment -> Access this computer from the Network

enter image description here