0

We do have 2 windows servers(xx.xx.xx.118 and xx.xx.xx.119) and also we do have 2 SQL servers installed on those windows servers(xx01 and xx02).

The customer gave us one separate username(user1) to login to windows and SQL servers but while trying to login to xx01 SQL server from 118 servers we are getting the Error:18456. We able to connect to xxo2 SQL server from 118 servers with the same user.

We are unable to connect to xx01 SQL server from 119 windows server also with the same username(user1). We are able to connect to xx02 SQL server from 119 windows server.

We also got the Administrator account details both windows servers but unfortunately, Admin is also not having access to SQL servers.

I have gone through different URLs and tried all the available options but couldn't get the access.

Tried to run as different user...to change any settings first we don't have any user able to access.

The customer has no idea regarding other users having access to SQL servers and also they are not aware of sa account details.

The windows are virtual machines and we RDP to those servers.

Can someone suggest me how to get access to those SQL servers and resolve the issue?

migrated from stackoverflow.com Jun 18 '18 at 10:19

This question came from our site for professional and enthusiast programmers.

  • Have you done what docs.microsoft.com/en-us/sql/relational-databases/errors-events/… suggests? – GSerg Jun 18 '18 at 8:04
  • Are you having trouble doing remote access or local ? Sorry, but your question does not make it clear . – SQLApostle Jun 18 '18 at 8:21
  • I am bit confused how to change the Trusted_connection. i am getting severity: 14 and state: 1 – Chowdary Jun 18 '18 at 8:27
  • I am able to access the Windows servers remotely but when i try to login management studio I am getting the error – Chowdary Jun 18 '18 at 8:28
  • Ok .. so are you able to log on to SQL server remotely ? If yes , then you might want to look at the SQL Server logs , and look at the failed login entries. – SQLApostle Jun 18 '18 at 8:36
0

During the installation there is option to choose what are users to be added as instance administrators.

Your customer should add you or your team Distribution list. You probably added the built-in Administrators group and now you're running as admin, that's the reason you cannot access the instance.

If yes, then is simply a mater of running the client process elevated ('Run as administrator').

Check it out: Connect to SQL Server When System Administrators Are Locked Out

Otherwise;

try to run SSMS (SQL Server Management Studio) on the server with 'Run As Admin' to check if you added builtin\administrators during install. If you cant find any user with enough rights then you have to add a login to the server and give it sysadmin rights.

Start the server in single user mode by starting cmd.exe on the server with administrative rights and running net stop mssqlserver and then net start mssqlserver /m then in the same window run SQLCMD.

Use the following:

CREATE LOGIN [DOMAIN\ADMINISTRATOR] FROM WINDOWS;
EXEC SP_ADDSRVROLEMEMBER 'DOMAIN\ADMINISTRATOR', 'SYSADMIN';
GO
EXIT

and then run net stop mssqlserver and net start mssqlserver.

If you are running a named instance the servicename is different - Check services.msc to see the name and you have to run SQLCMD with -S server\instance -E to connect

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy