MS SQL DBA

April 9, 2009 | By

Hi MS SQL DBAs,

Welcome to MS SQL DBA for discussing regarding MS SQL database for troubleshooting and tuning.

Thanks and regards,

Gitesh Trivedi

Be Sociable, Share!

Category: MS SQL DBA help

Comments (5)

Trackback URL | Comments RSS Feed

  1. Nirav Joshi says:

    Hi SQL DBA/Developer,

    Welcome to the world of the SQL Server on your day to day job you are facing challenges and adventures in the SQL Server.

    So I would say lets start it from now or never 
    Do you know about the SQL Server 2008 its great product but would also mention here great product needs good installation skills so when you perform and installation of the SQL server 2008 and it completed successfully and certainly you are very happy  and eager to open the management studio
    To see how your SQL server instance is is look like and when you connect with the SQL Server you got the following error in your error log of the SQL Server don’t worry here is the solution /work around

    2009-02-06 15:48:21.28 spid7s Recovery is complete. This is an informational message only. No user action is required.
    2009-02-06 15:50:36.95 Logon Error: 18456, Severity: 14, State: 11.
    2009-02-06 15:50:36.95 Logon Login failed for user XYZNirav’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: ]
    2009-02-06 16:01:23.53 Logon Error: 18456, Severity: 14, State: 11.
    2009-02-06 16:01:23.53 Logon Login failed for user XYZ Nirav’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: ]
    2009-02-06 16:05:46.90 Logon Error: 18456, Severity: 14, State: 11.
    2009-02-06 16:05:46.90 Logon Login failed for user XYZ Nirav’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: ]
    2009-02-06 16:06:32.68 Logon Error: 18456, Severity: 14, State: 8.
    2009-02-06 16:06:32.68 Logon Login failed for user ‘sa’. Reason: Password did not match that for the login provided. [CLIENT: ]

    – We installed SQL server 2008 successfully on the box but we are not able to connect with the SQL server instance using the windows authentication or using the SQL server login
    — Please check first with the protocols and alias if there is not protocols enable then
    –Pleas enable TCP/IP Named pipe and Shared Memory Protocol please check in the cliconfg and also in the SQL server configuration manager
    –For the open the client configuration go to the run prompt and type there cliconfg and hit enter
    — Now if the SQL server service and agent service was running under the domain account please make sure this domain account must be add to the Groups for the SQL server service which comes under the Local Users and Groups
    — Under the groups it will like this
    SQLServerMSSQLUser$nijosh2k3$KATMAI

    –please add the user name under this group
    — If the all looks good
    — we cannot login using the SA account and Any Domain Account.
    –“Login failed for user ‘SA’” or “login failed for user ‘domain account’” when trying to connect to a newly installed instance of SQL Server 2008.
    — SSMS able to connect to other Server SQL server.
    — Login Failed for the ‘SA’ (Error-18456)
    — This is very generic error
    — We are not sure that SQL Server successfully installed on the box.
    — We got the following error from the SQL server error.
    — when we try to open the SQL server using the single user mode we are able to connect to the SQL server using the SQLCMD utility
    — go to the command prompt and change the path to the SQL Server binn folder

    C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2K8RTMMSSQLBinnsqlservr.exe –m –c ( if it is default instance )

    C:Program FilesMicrosoft SQL ServerMSSQL10.SQL2K8RTMMSSQLBinn>sqlservr.exe –m –c -sSQL2k8RTM ( If it is named instance )

    –Now open the new command prompt or SQL Server management studio and try to connect with the instance name

    sqlcmd -E -sSQL2k8RTM

    — We were able to connect to the SQL server instance.
    — We created the new SQL server user and check it for testing and we were able to connect using that user.
    — But when we change SQL server to the normally we were not able to connect using the Windows Authentication and also from the SQL SA login
    — Interesting thing was there we were able to connect with the SQL Login account. But we don’t have the sysadmin rights to change the SA password
    — We decided to start the SQL server using the -c (Console Mode) and -m (Using the Single User mode).
    — It was started successfully and then we were able to login in to the server using the SQLCMD -E command.
    — After log in to the SQL server we gave the sysadmin right to the SQL login.
    — EXEC sp_addsrvrolemember ‘NIRAV’, ‘sysadmin';
    — Once this SQL login has sysadmin rights we change the SQL server SA login password.
    — Also add the windows user in to the sysadmin role because in SQL server 2008 by default there is no builtin/Admin.
    — Always remember when you install SQL Server 2008 you need to manually add the user to the sysadmin role.

    rgds.
    http://www.dbametrix.com/service.html

  2. Nirav Joshi says:

    Please let me know if you have any concern for the SQL Server

  3. venkat says:

    what is instance? and how we can install multiple instances in single computer?

  4. Nirav Joshi says:

    Hi Venkat,

    in SQL server you can have multiple insance with diff name running on the same server and you can have n number of database under the mutiple instances