broken SQL 2008 SP1 Web Edition (can not login with SSMS)

Question

Scenario:

My installation of SQL Server 2008 Web Edition SP1 was working properly.

Since I've recently joined Microsoft's Website Spark*, I removed SQL2008 and installed SQL 2008 again using my Website Spark edition and license from the MSDN download site.

Next, I updated SQL 2008 to SP1 (this is required because I'm running Windows 2008 Server R2 Web edition).

When I launch SSMS (SQL Server Management Studio), "User name" is "myhost\Administrator" and is greyed out so it can not be changed.

When I installed my Website Spark version, I did not include "myhost\Administrator" when I was configuring SQL 2008 service accounts.

Instead I created an administrator account "myhost\mySQLaccount".

ERROR MESSAGE:

Connect to Server
  (X)  Cannot connect to    (local)

   Additional information:
      Login failed for user 'myhost'Admistrator'
      (Microsoft SQL Server, Error:  18456)

I tried to use the SQL Server Configuration Manager to correct this problem but could not find any useful way to fix this issue.

How to I fix this problem?

Connect to Server ...
  Server type:          Database Engine
  Server name:           (local)
  Authentication:     Windows Authentication

Please advise.

Thank you.

Gerry

* http://www.microsoft.com/web/websitespark/default.aspx

Answer 1

The username is grayed out when the mode of authentication is "Windows Authentication" in the login window.

Change the Authentication mode to "SQL Server Authentication" and you should be able to edit the login name to "myhost\mySQLaccount".

Answer 2

To resolve this issue, you must add an account to the Database Creators role and to the Security Administrators role in SQL Server. You add the account that the application pool is running as. To do this:

1. Add the account that the application pool is running as to the Security Administrators role and to the Database Creators role in SQL Server. To do this: 1. Start SQL Server Enterprise Manager. 2. Expand Microsoft SQL Servers, expand the server group, expand the appropriate server, and then expand Security. 3. Right-click Logins, and then click New Login. 4. Type the account name in the Name box. Use the following format for the account name: DomainName\AccountName Note If SQL Server is installed on a separate computer and you want to run the application pool as the Network Service account, the computer that is running Windows SharePoint Services must have permissions to access the remote computer that is running SQL Server. On the remote computer that is running SQL Server, add the DomainName\ServerName$ account to the Database Creators role and to the Security Administrators role. 5. Click the Server Roles tab. 6. In the Server Role list, click to select the Security Administrators check box, and then click to select the Database Creators check box. Click OK. 7. Quit SQL Server Enterprise Manager.
2. On the Configure Administrative Virtual Server page of SharePoint Central Administration, create a new application pool, specify the account as the application pool account, and then click OK. The Application Pool Changed page is displayed.
3. Restart Microsoft Internet Information Services (IIS). To do this, click Start, click Run, type iisreset in the Open box, and then click OK.
4. On the Application Pool Changed page, click OK. On the Set Configuration Database Server page of SharePoint Central Administration, specify the configuration database settings.

MS KB

Answer 3

Resolved this way:

(a) logged off (b) logged on using SQL Server 2008 Admin account (c) running ssms as sysadmin, added a new account for myhost\Administrator with sysadmin privileges. (d) logged off (e) logged back in as myhost\Administrator. (f) success, Admin account is now okay for ssms 2008

@Raj More and @drohan ... thank you both for your input.

Regards, Gerry (Lowry)