Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MS SQL 2012 Cant login to Studio Manager

Posted on 2014-08-21
8
Medium Priority
?
345 Views
Last Modified: 2014-09-01
We have a 2012 Server running SQL 2012, recently the domain membership of the server was changed and now we cant login to studio manager i assume because the new domain admin account has no permissions.

I dont have the SA password or even know if SQL authentication was selected during install.

How can i add the new domain admin account as a sysadmin i assume to allow access.

Thanks,
Craig
0
Comment
Question by:NSSUK
  • 4
  • 3
8 Comments
 
LVL 12

Expert Comment

by:Vaseem Mohammed
ID: 40276751
Have u tried logging in with local administrator account of sql server?
0
 
LVL 6

Expert Comment

by:Sir Learnalot
ID: 40276761
Log in as the local system administrator (SERVERNAME\Administrator)
Open the SQL Management Studio
Log in using Windows Authentication
Go to Security-> Logins
Add your domain admin login
Go to Server Roles
Add that domain admin account to the sysadmin role.

Let me know if this works
0
 

Author Comment

by:NSSUK
ID: 40279161
Hi,

I still get the same login issue as a local admin - below is the message i get

Cannot connect to DPG-SAGE\SAGE.

===================================

Login failed for user 'DPG-SAGE\Administrator'. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

------------------------------
Server Name: DPG-SAGE\SAGE
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536


------------------------------
Program Location:

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData)
   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()
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 6

Expert Comment

by:Sir Learnalot
ID: 40279173
Try logging into DPG-SAGE directly, are you able to?

My feeling is that you have forgotten the initial Administrator password you used to setup the SQL instance...

Also note the following:

If you are trying to connect using SQL Server Authentication, verify that SQL Server is configured in Mixed Authentication Mode.

If you are trying to connect using SQL Server Authentication, verify that SQL Server login exists and that you have spelled it properly.

If you are trying to connect using Windows Authentication, verify that you are properly logged into the correct domain.
0
 

Author Comment

by:NSSUK
ID: 40279193
i logged into the local machine not domain, sql studio says im using dpg-sage\administrator for windows authentication so that seems correct but get the previous error ?
0
 
LVL 6

Expert Comment

by:Sir Learnalot
ID: 40279197
Did you try connecting to DPG-SAGE instead of DPG-SAGE\SAGE. You should see some databases there in the root sql instance
0
 

Author Comment

by:NSSUK
ID: 40279209
yes did try that but got the same message
0
 
LVL 6

Accepted Solution

by:
Sir Learnalot earned 2000 total points
ID: 40279238
I dont know why I didnt think of this... Change back to your old domain if possible, log in SQL, add the new login and make sure the local system admin login is there, next go to server roles and make sure they are both sysadmin. Then you can go back and rejoin your new domain and use your new login!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question