0

I have a virtual machine (vmware) with Windows Server 2008 R2 x64 and SQL Server 2008 R2 installed.

I see 2 SQL Server services on this vm : sqlexpress, mssqlserver.

Both of them are use the default Network Service account to login.

And both of them are running (I see the green color).

I successfully connect to the instance MyMachine\sqlexpress, but did not succeed to connect to the mssqlserver instance on MyMachine.

I get an error

Login failed for user 'MyUser'. (Microsoft SQL Server, Error: 18456)

There are no registered servers, and when I try create a new registered server, I get the same error.

Why?

Improve this question
1
  • There are no registered servers because you have to register them to start.
    – Mike Walsh
    May 2, 2011 at 21:14

2 Answers 2

Reset to default
1

Have you verified that the user account MyUser has logon rights to that server?

If you are using Active Directory authentication then the user needs to either be given explicit permission to connect to the database server, or be a member of a group that has that permission.

Improve this answer
2
  • That is my guess as well. When that instance was installed two questions should have popped up 1.) What is the SA password (if configuring in mixed mode) and 2.) Which accounts do you wish to add explicitly to the instance as a SQL Sysadmin? Starting in SQL Server 2008 only those explicitly added will have access. Prior to SQL 2008 by default everyone in the local machine's administrator group were granted access. So either find what account was added or get the SA password. If still no luck, there are other ways but more difficult.
    – Mike Walsh
    May 2, 2011 at 21:20
  • i solved the problem by reinstalling sql server 2008, we forgot the sa pwd. thanks.
    – yaron
    May 3, 2011 at 9:08
0

To a user trying to log in, SQL is intentionally vague about the cause of login failures in order to prevent attack.

Look at the SQL Server ERRORLOG for the SQL Server instance you are having trouble with. In SQL Server Management Studio, expand the Management node, then SQL Server Logs then have a look at the 'Current' log (assuming SQL Server has not been restarted or the logs recycled since you last got this error). If you have no way of connecting, you can look in the ERRORLOG file in the logs directory where SQL is installed.

This should give you some additional information which will help troubleshoot further.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .