Only SA can log in remote to MS SQL 2014

Hi

I am able to connect remotely to a MS SQL Server Manager (from another MS SQL Server Manager) using the built-in SA account, but any other account I create on the remote SQL servergets access denied. I give the new user same privileges as the SA account. What could be the problem?

Please post the scripts creating thing the other accounts and screenshots and messages of your login attempts

When I try to log in using any other account than SA the following error occurs:

"Login failed for user 'KMT'. (.Net SqlClient Data Provider)"


Server Name: Remote server IP\VISMA, 11433
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536


Program Location:

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 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, TaskCompletionSource1 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 Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()


User KMT have sysadmin rights on remote SQL. I am not an SQL guru, must be something I am missing here.

The user KMT can log in locally on the remote SQL.

Regards
Pål Berg

Are you sure about this port number? Standard port is 1433

Hi

Yes the port is OK. 1433 was already taken in the firewall, so we had to change it for this connection. And the SA account connects OK through this port (11433).

Regards
Pål Berg

Do you have SSMS Installed (you can get it for free). Or even just SQLCMD
Try connecting using the KMT id remotely using one of those programs. you may get better error messages

1 Like

One of my colleagues figured this one out; Because this is a named SQL instance you have to set the dynamic port to a fixed port in the TCP/IP settings for the instance in SQL Configuration Manager. Once I did that any user can connect without problems.

Thanks to gbritton for participating.

1 Like