Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com May 2 '11 at 18:53

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

    
There are no registered servers because you have to register them to start. –  Mike Walsh May 2 '11 at 21:14

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
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 '11 at 21:20
    
i solved the problem by reinstalling sql server 2008, we forgot the sa pwd. thanks. –  yaron May 3 '11 at 9:08

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.