question

BharatS-3892 avatar image
0 Votes"
BharatS-3892 asked ShivamKumar commented

SQL Server Management studio 2019

Hello Friends

I recently download SQL server on my desktop and also Microsoft SQL Studio version 18

I am really new to this , need some assistance

I created a DB by name Automic and Logins Automic. But when i try to login , it is getting failed . Not sure what im missing , could you please help me with that

Error message :-
%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476


Server Name: DESKTOP-O3FCHKN
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, SqlAuthenticationProviderManager sqlAuthProviderManager)
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, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 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()


could you please advice .

84128-db-and-id.png84028-id-2.png84029-err-1.png


sql-server-general
db-and-id.png (20.8 KiB)
id-2.png (11.4 KiB)
err-1.png (11.0 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @BharatS-3892,

We have not received a response from you. Did the replies could help you? If the response helped, do "Accept Answer". If it is not, please let us know. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·

@VaibhavChaudhari Could you elaborate on you answer regarding 'Security and enable SQL server and windows mix mode' ? I cannot find that in Windows Server 2019 Data Center build 1809.

Since I cannot connect, navigating the Object Explorer is not an option.

I get exactly the same error messages as @BharatS-3892

0 Votes 0 ·
ShivamKumar avatar image ShivamKumar FarrerAndrewAdmin-9995 ·

Hi @FarrerAndrewAdmin-9995

Sql server has two login types for authentication one is sql server login where you need to enter user name and password and another is windows login where your windows creds are automatically used by sql server to provide you the integrated authentication without the need of entering password again.

Mixed mode authentication only works when enabled.

If you have forgotton your password for sql server login you will need to check with your DBA or if you had windows authentication you will need to be a part of AD security group which has permission to login in sql server , which one, this you will need to check with your team or manager for whom its working DBA can also provide you this info and then ask your AD team to add you in it.

Incase its your personal computer and server and you have forgotton password you can reset it by starting sql server in single user mode but thats an problem to solve with a different question so in case you still need help you can open another question and make sure you are chosing the right authentication type whiel trying to login using SSMS

Regards,
Shivam

0 Votes 0 ·
VaibhavChaudhari avatar image
1 Vote"
VaibhavChaudhari answered

Try right click on server "Desktop..." > Properties > Security and enable SQL server and windows mix mode.

Right click again on server and restart and then try connecting using Atomic login.



Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @BharatS-3892,

There are two authentication modes for SQL server( windows authentication and Mix authentication). When you using Login Automic connect to SQL server, this login process using SQL server authentication. By default, we did not enable SQL server authentication. So you need to enable SQL server authentication as VaibhavChaudhari mentioned.

84329-screenshot-2021-04-05-111356.jpg

Please restart SQL server service to make this change effect.

If you want learn more about SQL server authentication, please refer to MS document Choose an Authentication Mode.


If the response is helpful, please click "Accept Answer" and upvote it, thank you.




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.