January 28, 2011 at 5:43 pm
I'm really new to SQL, but decided to install the Eval Version of SQL 2008 to learn more about it.
I just completed the installation, so I'm attempting to log into Management Studio for the first time. At the login screen, when I choose SQL Authentication in the Authentication dropdown using the sa account I get:
Cannot connect to [computer name] Addional information: Login failed for user 'sa'. (Microsoft SQL Server, Error:18456).
For the password for the 'sa' account, I put in the password that I entered at the Server Configuration Page in the SQL Server 2008 Wizard. I wasn't sure what else I was supposed to put in here, as I don't recall setting up any other users or passwords during the install process.
When I switch to Windows Authentication, it pulls up my local username and when I click Connect I get the same error.
What's going on, and how can I get authenticated? I must admit that I don't remember what I entered in the Account Name column for the SQL Server Agent or Database Engine Services, might that be the issue?
I ran a repair of the install, but that didn't fix the issue. I noticed in Computer Management, that almost all of the SQL-related services have the 'NT AUTHORITY\NETWORK SERVICE' user noted in the 'Log On As' column.
Please help. I'm hoping that something I said above provides a clue for you. Thanks in advance.
January 28, 2011 at 10:23 pm
Is your Windows account in the local administrators group, if not you can try adding it in computer management and then connect using Windows authentication. By default a new install adds local admins as sysadmins in SQL Server, you can then login and reset the sa password.
I assume this is the first time anyone has tried to connect to the instance. If not, and someone removed the local admins group you can still get in but you would need to startup in single user mode.
January 29, 2011 at 6:27 am
I just confirmed that the user I'm logged into the machine as is indeed in the local Administrators group.
Should I now try to get in using single-user mode? I read up on single-user mode, and I read to do this one should/can use the sqlcmd utility. How do I run this? I saw this article, but I'm still not clear on where or how you launch this utility.
January 29, 2011 at 6:49 am
I hope you are connecting to correct instance. Did you configure the default instance or named.
To confirm on this, you could navigate to below folder path
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log (this path may not exactly match yours). In general, you could navigate to the path where you could see the errorlog.
There would be a file named ERRORLOG. Open the file and search for below.
Server name is 'your server'. This is an informational message only. No user action is required
Just connect to that server you see in log file and give your login crendentials (SA and password).
My guess you may not be logging on to the correct server.
M&M
January 29, 2011 at 7:47 am
Thanks for the feedback. I went into the Errorlog and confirmed that I have the correct server name in the 'Server Name' dropdown on the login screen of SSMS.
I just remembered, that on the 'Complete' screen in the Sql Server setup wizard, that in the 'Information about this Setup operation or possible next steps:', that one of the messages had a red x next to it and said something about "Access Denied." Unfortunately, I didn't write down the exact message.
I later ran a repair install, and didn't get that message at the completion of the repair, however.
Might that have something to do with what's going on here?
January 29, 2011 at 9:22 am
Can you post the contents of the ERRORLOG.
Also, verify if the SQL Services are running.
Go to
Start-> All Programs-> Microsoft SQL Server 2008 R2-> Configuration Tools-> SQL Server Services->
Check SQL SErver and SQL Server Agent
M&M
January 29, 2011 at 11:10 am
When I go to Start-> All Programs-> Microsoft SQL Server 2008 R2-> Configuration Tools, I don't see a SQL Server Services option. All I have there under Configuration Tools is Reporting Services Configuration Manager, SQL Server Configuration Manager, SQL Server Error and Usage Reporting, and SQL Server Installation Center.
Below are the contents of my Error Log:
2011-01-28 12:59:56.37 Server Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
2011-01-28 12:59:56.44 Server (c) 2005 Microsoft Corporation.
2011-01-28 12:59:56.44 Server All rights reserved.
2011-01-28 12:59:56.44 Server Server process ID is 4576.
2011-01-28 12:59:56.44 Server Authentication mode is MIXED.
2011-01-28 12:59:56.44 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2011-01-28 12:59:56.56 Server Registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2011-01-28 12:59:56.56 Server Command Line Startup Parameters:
-m SqlSetup
-f
-Q
-q SQL_Latin1_General_CP1_CI_AS
-T 4022
-T 4010
-T 3659
-T 3610
2011-01-28 12:59:56.69 Server Warning: The server instance was started using minimal configuration startup option (-f). Starting an instance of SQL Server with minimal configuration places the server in single-user mode automatically. After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.
2011-01-28 12:59:56.74 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2011-01-28 12:59:56.74 Server Detected 1 CPUs. This is an informational message; no user action is required.
2011-01-28 12:59:59.23 Server Perfmon counters for resource governor pools and groups failed to initialize and are disabled.
2011-01-28 12:59:59.37 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2011-01-28 13:00:01.53 Server Node configuration: node 0: CPU mask: 0x00000001 Active CPU mask: 0x00000001. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2011-01-28 13:00:01.99 Server Support for distributed transactions was not enabled for this instance of the Database Engine because it was started using the minimal configuration option. This is an informational message only. No user action is required.
2011-01-28 13:00:02.24 spid7s Warning ******************
2011-01-28 13:00:02.26 spid7s SQL Server started in single-user mode. This an informational message only. No user action is required.
2011-01-28 13:00:03.22 spid7s Starting up database 'master'.
2011-01-28 13:00:07.32 spid7s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2011-01-28 13:00:07.81 spid7s Recovery completed for database master (database ID 1) in 2 second(s) (analysis 202 ms, redo 670 ms, undo 896 ms.) This is an informational message only. No user action is required.
2011-01-28 13:00:14.58 spid7s SQL Server was started using the -f flag. SQL Server Audit is disabled. This is an informational message. No user action is required.
2011-01-28 13:00:14.75 spid7s FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
2011-01-28 13:00:14.79 spid7s Server started with '-f' option. Auditing will not be started. This is an informational message only; no user action is required.
2011-01-28 13:00:14.94 spid7s Starting up database 'mssqlsystemresource'.
2011-01-28 13:00:15.49 spid7s The resource database build version is 10.00.1600. This is an informational message only. No user action is required.
2011-01-28 13:00:19.66 spid8s Starting up database 'model'.
2011-01-28 13:00:19.91 spid7s Server name is 'HPXP1'. This is an informational message only. No user action is required.
2011-01-28 13:00:20.61 spid7s Informational: No full-text supported languages found.
2011-01-28 13:00:21.76 spid8s Clearing tempdb database.
2011-01-28 13:00:27.83 Server A self-generated certificate was successfully loaded for encryption.
2011-01-28 13:00:27.95 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2011-01-28 13:00:28.01 Server Dedicated administrator connection support was not started because it is disabled on this edition of SQL Server. If you want to use a dedicated administrator connection, restart SQL Server using the trace flag 7806. This is an informational message only. No user action is required.
2011-01-28 13:00:28.20 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
2011-01-28 13:00:28.20 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2011-01-28 13:00:34.49 spid8s Starting up database 'tempdb'.
2011-01-28 13:00:38.32 spid7s Recovery is complete. This is an informational message only. No user action is required.
2011-01-28 16:03:58.06 Logon Error: 18461, Severity: 14, State: 1.
2011-01-28 16:03:58.06 Logon Login failed for user 'HPXP1\AG'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: <local machine>]
2011-01-28 16:07:18.11 Logon Error: 18456, Severity: 14, State: 8.
2011-01-28 16:07:18.11 Logon Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]
2011-01-28 16:08:45.08 Logon Error: 18461, Severity: 14, State: 1.
2011-01-28 16:08:45.08 Logon Login failed for user 'HPXP1\AG'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: <local machine>]
2011-01-28 16:08:59.72 Logon Error: 18456, Severity: 14, State: 5.
2011-01-28 16:08:59.72 Logon Login failed for user 'HPXP1\AG'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
2011-01-28 16:12:15.71 Logon Error: 18461, Severity: 14, State: 1.
2011-01-28 16:12:15.71 Logon Login failed for user 'HPXP1\AG'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: <local machine>]
2011-01-28 16:12:31.03 Logon Error: 18461, Severity: 14, State: 1.
2011-01-28 16:12:31.03 Logon Login failed for user 'HPXP1\AG'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: <local machine>]
2011-01-28 16:17:13.68 Logon Error: 18461, Severity: 14, State: 1.
2011-01-28 16:17:13.68 Logon Login failed for user 'HPXP1\AG'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: <local machine>]
2011-01-28 16:17:49.31 Logon Error: 18456, Severity: 14, State: 8.
2011-01-28 16:17:49.31 Logon Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]
2011-01-28 16:17:58.59 Logon Error: 18461, Severity: 14, State: 1.
2011-01-28 16:17:58.59 Logon Login failed for user 'HPXP1\AG'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: <local machine>]
2011-01-28 16:18:29.31 Logon Error: 18461, Severity: 14, State: 1.
2011-01-28 16:18:29.31 Logon Login failed for user 'HPXP1\AG'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: <local machine>]
2011-01-28 16:19:02.44 Logon Error: 18461, Severity: 14, State: 1.
2011-01-28 16:19:02.44 Logon Login failed for user 'HPXP1\AG'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: <local machine>]
2011-01-28 16:19:07.32 Logon Error: 18461, Severity: 14, State: 1.
2011-01-28 16:19:07.32 Logon Login failed for user 'HPXP1\AG'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: <local machine>]
2011-01-28 16:19:55.13 Logon Error: 18456, Severity: 14, State: 5.
2011-01-28 16:19:55.13 Logon Login failed for user 'hpxp1\ag'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
2011-01-28 16:20:07.41 Logon Error: 18456, Severity: 14, State: 5.
2011-01-28 16:20:07.41 Logon Login failed for user 'hpxp1/ag'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
2011-01-28 16:20:18.23 Logon Error: 18456, Severity: 14, State: 8.
2011-01-28 16:20:18.23 Logon Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]
2011-01-28 16:20:24.42 Logon Error: 18456, Severity: 14, State: 8.
2011-01-28 16:20:24.42 Logon Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]
2011-01-28 16:21:47.68 Server SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.
January 29, 2011 at 12:18 pm
SQL Dude-467553 (1/28/2011)
By default a new install adds local admins as sysadmins in SQL Server
Not in SQL Server 2008 it doesn't!
Vadon, during the installation you would have run through the database engine configuration section. The tab called Account Provisioning details the authentication and the accounts enabled.
At this point, did you select windows or sql server authentication?
Which windows accounts did you add to the Specify SQL Server Administrators section, you are required to add at least one?
Don't start messing with single user mode at the present time
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 29, 2011 at 12:23 pm
Perry Whittle (1/29/2011)
SQL Dude-467553 (1/28/2011)
By default a new install adds local admins as sysadmins in SQL ServerNot in SQL Server 2008 it doesn't!
Vadon, during the installation you would have run through the database engine configuration section. The tab called Account Provisioning details the authentication and the accounts enabled.
At this point, did you select windows or sql server authentication?
Which windows accounts did you add to the Specify SQL Server Administrators section, you are required to add at least one?
Don't start messing with single user mode at the present time
Good point. Though if you notice the log files show mixed mode is enabled and there are startup options configured -m and -f. Minimal configuration is enabled.
January 29, 2011 at 12:29 pm
Perry, on the Account Provisioning tab, I selected Mixed Mode. Any more ideas as to what I can do to log in? Thanks a lot!
January 29, 2011 at 12:53 pm
vadon97 (1/29/2011)
Perry, on the Account Provisioning tab, I selected Mixed Mode. Any more ideas as to what I can do to log in? Thanks a lot!
Which windows account(s) did you add?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 29, 2011 at 1:40 pm
I honestly don't recall creating any accounts. In the Sql Server Installation wizard, in the Server Configuration Section, under The Service Accounts tab I remember I had to specify the Account Name for the Server Agent, Server Database Engine and SSIS. I don't remember what was specified in the Account Name fields for each, if that's what you're asking.
If it helps at all, in Computer Management, SQL Server Agent and SQL Server Integration Services both have "NT AUTHORITY\NETWORK SERVICE" in the 'Log On As' column. I noticed that there isn't a SQL Server Database Engine service showing up at all here in Computer Management, in case that helps at all.
January 29, 2011 at 1:46 pm
You don't create accounts you add existing windows accounts into the sysadmin role.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 29, 2011 at 3:39 pm
Perry is talking about windows account that was granted permissions during the wizard to be a sysadmin for the SQL Server. Looks like you are trying to connect using a user called AG. If you don't remember what users you granted permission and you don't know the sa password you can't connect under normal startup.
Now honestly since it's a test machine and not a production box I notice you have startup options for -m and -f. Seeing the log files SQL is currently starting up in single user mode with minimum configuration. You can google and see what those do and where they are set in SQL Configuration Manager. If it's in that state only one admin connection is allowed and no others can connect so something else like the SQL agent could be grabbing that one connection.
If was testing I'd remove both those startup options, restart SQL and try connecting again under normal conditions with both the sa account choosing SQL authentication and your windows account making sure to change the drop down to
windows authentication. If you could then post the log file again we should still see login failed but SQL started normally. After that point if you don't know a valid principal to connect with I believe you will need to follow the correct steps to start in single user mode to recover the sa password but there are a few steps involved in order to grab the one admin connection.
Just my opinion....
January 29, 2011 at 4:55 pm
I'm logged in as a user that's a part of the local Administrators group. If so, shouldn't I be able to log in since it's in single user mode?
Also, when I went into Startup Parameters to try to remove -m and -f, I didn't see -m or -f at the beginning of the Startup Parameters. The way I interpreted the instructions on this page about how to modify parameters (and remove the -m and -f) I didn't see either letter. Below is a copy/paste of what was in that box:
-dC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
How could -m and -f show up in the log file, but not when I look at the Properties for the SQL Server service? Any ideas on how I can resolve this?
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply