locked
Unable to login after installing SQL Server 2008 R2 Standard Ed on a Windows Server 2003 Box RRS feed

  • Question

  • Hi, I've installed SQL Server 2008 R2 Standard Edition on two seperate boxes. One of them works absolutely fine, while on the other one i'm unable to login to Studio Manager (after a successful install) with both Windows and SQL (using the 'sa' account) authentication schemes. The only obvious difference b/w the two boxes are:

    Computer Name:

    Server 1: testserver1 (Domain:ds.tel.com)

    Server 2: testserver2 (Workgroup: TEL)

    Local Admin User Accounts:

    Server 1: Administrator

    Server 2: TelSec

    I used the local admin accounts to perform the install, and the following INI file was used:

    ;SQLSERVER2008 Configuration File

    [SQLSERVER2008]

    ; Specify the Instance ID for the SQL Server features you have specified. SQL Server directory structure, registry structure, and service names will reflect the instance ID of the SQL Server instance.

    INSTANCEID="NOS"

    ; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter.

    ACTION="Install"

    ; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, and Tools. The SQL feature will install the database engine, replication, and full-text. The Tools feature will install Management Tools, Books online, Business Intelligence Development Studio, and other shared components.

    FEATURES=SQLENGINE,REPLICATION,FULLTEXT,AS,RS,BIDS,CONN,IS,BC,SDK,BOL,SSMS,ADV_SSMS,SNAC_SDK,OCS

    ; Displays the command line parameters usage

    HELP="False"

    ; Specifies that the detailed Setup log should be piped to the console.

    INDICATEPROGRESS="False"

    ; Setup will not display any user interface.

    QUIET="False"

    ; Setup will display progress only without any user interaction.

    QUIETSIMPLE="True"

    ; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system.

    X86="False"

    ; Detailed help for command line argument ENU has not been defined yet.

    ENU="True"

    ; Parameter that controls the user interface behavior. Valid values are Normal for the full UI, and AutoAdvance for a simplied UI.

    ;UIMODE="Normal"

    ; Specify if errors can be reported to Microsoft to improve future SQL Server releases. Specify 1 or True to enable and 0 or False to disable this feature.

    ERRORREPORTING="False"

    ; Specify the root installation directory for native shared components.

    INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"

    ; Specify the installation directory.

    INSTANCEDIR="C:\Program Files\Microsoft SQL Server"

    ; Specify that SQL Server feature usage data can be collected and sent to Microsoft. Specify 1 or True to enable and 0 or False to disable this feature.

    SQMREPORTING="False"

    ; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express editions. This parameter is required when installing the SQL Server Database Engine (SQL), Analysis Services (AS), or Reporting Services (RS).

    INSTANCENAME="NOS_SERVER"

    ; Agent account name

    AGTSVCACCOUNT="NT AUTHORITY\SYSTEM"

    ; Auto-start service after installation.

    AGTSVCSTARTUPTYPE="Automatic"

    ; Startup type for Integration Services.

    ISSVCSTARTUPTYPE="Automatic"

    ; Account for Integration Services: Domain\User or system account.

    ISSVCACCOUNT="NT AUTHORITY\SYSTEM"

    ; The name of the account that the Analysis Services service runs under.

    ASSVCACCOUNT="NT AUTHORITY\SYSTEM"

    ; Controls the service startup type setting after the service has been created.

    ASSVCSTARTUPTYPE="Automatic"

    ; The collation to be used by Analysis Services.

    ASCOLLATION="Latin1_General_CI_AS"

    ; The location for the Analysis Services data files.

    ASDATADIR="C:\Program Files\Microsoft SQL Server\MSAS10_50.NOS\OLAP\Data"

    ; The location for the Analysis Services log files.

    ASLOGDIR="C:\Program Files\Microsoft SQL Server\MSAS10_50.NOS\OLAP\Log"

    ; The location for the Analysis Services backup files.

    ASBACKUPDIR="C:\Program Files\Microsoft SQL Server\MSAS10_50.NOS\OLAP\Backup"

    ; The location for the Analysis Services temporary files.

    ASTEMPDIR="C:\Program Files\Microsoft SQL Server\MSAS10_50.NOS\OLAP\Temp"

    ; The location for the Analysis Services configuration files.

    ASCONFIGDIR="C:\Program Files\Microsoft SQL Server\MSAS10_50.NOS\OLAP\Config"

    ; Specifies whether or not the MSOLAP provider is allowed to run in process.

    ASPROVIDERMSOLAP="1"

    ; Specifies the list of administrator accounts that need to be provisioned.

    ASSYSADMINACCOUNTS="TESTSERVER1\Administrator"

    ; A port number used to connect to the SharePoint Central Administration web application.

    FARMADMINPORT="0"

    ; Startup type for the SQL Server service.

    SQLSVCSTARTUPTYPE="Automatic"

    ; Level to enable FILESTREAM feature at (0, 1, 2 or 3).

    FILESTREAMLEVEL="0"

    ; Set to "1" to enable RANU for SQL Server Express.

    ENABLERANU="False"

    ; Specifies a Windows collation or an SQL collation to use for the Database Engine.

    SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"

    ; Account for SQL Server service: Domain\User or system account.

    SQLSVCACCOUNT="NT AUTHORITY\SYSTEM"

    ; Windows account(s) to provision as SQL Server system administrators.

    SQLSYSADMINACCOUNTS="TESTSERVER1\Administrator"

    ; The default is Windows Authentication. Use "SQL" for Mixed Mode Authentication.

    SECURITYMODE="SQL"

    ; Provision current user as a Database Engine system administrator for SQL Server 2008 R2 Express.

    ADDCURRENTUSERASSQLADMIN="False"

    ; Specify 0 to disable or 1 to enable the TCP/IP protocol.

    TCPENABLED="1"

    ; Specify 0 to disable or 1 to enable the Named Pipes protocol.

    NPENABLED="0"

    ; Startup type for Browser Service.

    BROWSERSVCSTARTUPTYPE="Automatic"

    ; Specifies which account the report server NT service should execute under. When omitted or when the value is empty string, the default built-in account for the current operating system.

    ; The username part of RSSVCACCOUNT is a maximum of 20 characters long and

    ; The domain part of RSSVCACCOUNT is a maximum of 254 characters long.

    RSSVCACCOUNT="NT AUTHORITY\SYSTEM"

    ; Specifies how the startup mode of the report server NT service. When

    ; Manual - Service startup is manual mode (default).

    ; Automatic - Service startup is automatic mode.

    ; Disabled - Service is disabled

    RSSVCSTARTUPTYPE="Automatic"

    ; Specifies which mode report server is installed in.

    ; Default value: "FilesOnly"

    RSINSTALLMODE="DefaultNativeMode"

    ; Add description of input argument FTSVCACCOUNT

    FTSVCACCOUNT="NT AUTHORITY\SYSTEM"

    IACCEPTSQLSERVERLICENSETERMS="True"

    SAPWD="Test12"

    Tuesday, June 21, 2011 4:48 PM

Answers

  • The complete issue was due to a Windows OS Permissions issue. I wish the logs were more helpful. For resolution see

    http://support.microsoft.com/kb/2000257

    • Marked as answer by dct374 Friday, July 1, 2011 2:43 PM
    Friday, July 1, 2011 2:43 PM

All replies

  • Hello there.

    Please check in the SQL Server Configuration Manager whether the SQL Server service itself is started. Also, as a side note, it's better to create a separate user for the SQL Server service(s) as opposed to using the system account.

    best regards,
    Calin 

    Wednesday, June 22, 2011 6:23 AM
  • Hi Calin, thanks for your reply. The SQL Server Configuration Manager seems identical to me for both Servers. Is there anything in particular that i need to look for ?

    Wednesday, June 22, 2011 11:00 AM
  • Also note that the first time i tried logging in, it showed the following error on the faulty server : Unable to login as the Server is running in Single-User Mode. And the rest of the times it simply states "login Failed". I'm not sure what caused this as the installation was completely identical on both servers (with windows admin accounts). Could this be an issue with the way windows is installed ?
    Wednesday, June 22, 2011 3:45 PM
  • When you get login failed error, SQL Server would make an entry in SQL Server ERRORLOG.
    Can you please try logging in, note the time of failure and check in ERRORLOG. Post the exact message which you see in ERRORLOG.
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Wednesday, June 22, 2011 3:56 PM
  • Hi dct374,

     

    In your configuration.ini file, you add the account [TESTSERVER1\Administrator] as the sysadmin in both SQL Server instances, please try use it to log in.

     

    However, Balmukund is right, please first check the error log for the login failed error and identify the reason, please have the look at the below blog about such errors and solution:

    http://sql-articles.com/articles/troubleshooting/troubleshooting-login-failed-error-18456/

     

    Thanks,
    Weilin Qiao


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    Thursday, June 23, 2011 8:40 AM
  • Hi Weiln Qiao, for the second server i used [TESTSERVER2\TelSec] as TelSec is the administrator account on this server. I will have a look at the error log and post it.

    Best Regards

    Thursday, June 23, 2011 10:30 AM
  • Ok, this is what i see from the error log:

    For Sa account login

    ------------------------

    Error: 18456, Severity: 14, State: 11.

    For Windows Authentication login

    ---------------------------------------

    Login failed for user 'TESTSERVER2\TelSec'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 10.1.233.12]

    Thursday, June 23, 2011 3:11 PM
  • Ok, this is what i see from the error log:

    For Sa account login

    ------------------------

    Error: 18456, Severity: 14, State: 11.

    For Windows Authentication login

    ---------------------------------------

    Login failed for user 'TESTSERVER2\TelSec'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 10.1.233.12]


    Below is one pair of error for "TESTSERVER2\TelSec" login
    {
    Error: 18456, Severity: 14, State: 11.
    Login failed for user 'TESTSERVER2\TelSec'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 10.1.233.12]
    }

    Did you ever delete TelSec account? http://blogs.msdn.com/b/sqlserverfaq/archive/2010/10/27/troubleshooting-specific-login-failed-error-messages.aspx

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Thursday, June 23, 2011 4:23 PM
  • You should see same pair for sa account also.. can you please share that?


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Thursday, June 23, 2011 4:24 PM
  • Hi Balmukund, many thanks for taking time to review this issue. I can confirm that we never deleted the TelSec account (this is the admin and only account existing on the Server). As far as the SA account is concerned i see the following error message (even though the password was exactly the same as specified at the time of installation, we have a standard INI file that we use for each server, and it is only this particular server that is giving these problems):

     Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]

    Also going through the logs i also see that the same error message can be seen for the TelSec account at times, e.g.

    Login failed for user 'TESTSERVER\Telsec'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

    I wonder why it shows [CLIENT: <local machine>] sometimes and [CLIENT: 10.1.233.12], i.e. the local IP address other times. Please note that even though the Server has an IP address assigned it has not yet been connected to a network yet.

    Tuesday, June 28, 2011 10:50 AM
  • Hi Balmukund, many thanks for taking time to review this issue. I can confirm that we never deleted the TelSec account (this is the admin and only account existing on the Server). As far as the SA account is concerned i see the following error message (even though the password was exactly the same as specified at the time of installation, we have a standard INI file that we use for each server, and it is only this particular server that is giving these problems):

     Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]

    Also going through the logs i also see that the same error message can be seen for the TelSec account at times, e.g.

    Login failed for user 'TESTSERVER\Telsec'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

    I wonder why it shows [CLIENT: <local machine>] sometimes and [CLIENT: 10.1.233.12], i.e. the local IP address other times. Please note that even though the Server has an IP address assigned it has not yet been connected to a network yet.

    1. Can't you reset the sa password?
    2. Check Select * from sys.server_principals and verify that you have account 'TESTSERVER\Telsec'
    3. Was the server renamed?
    4. Did someone restore master database?

    If you are completly out and not able to login by any means then you can use below trick.
    http://msdn.microsoft.com/en-us/library/dd207004.aspx

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Tuesday, June 28, 2011 11:22 AM
  • Hi Balmukand,

                       No i can't reset the SA password, as I'm unable to login at all using both Windows or SQL authentication (I've tried both Studio Manager and SQLCMD). Also i can't perform number 2 for the same reason. The server was not renamed and the master database for not renamed. I literally just installed SQL Server on this box a week ago. I have a standard INI file and setup instructions that i follow to install on each box (and this is the only particular box that gave this issue). I just want to find out the root cause of it to avoid having this issue in the future.

    Best Regards

    Tuesday, June 28, 2011 12:04 PM
  • Hi Balmukand,

                       No i can't reset the SA password, as I'm unable to login at all using both Windows or SQL authentication (I've tried both Studio Manager and SQLCMD). Also i can't perform number 2 for the same reason. The server was not renamed and the master database for not renamed. I literally just installed SQL Server on this box a week ago. I have a standard INI file and setup instructions that i follow to install on each box (and this is the only particular box that gave this issue). I just want to find out the root cause of it to avoid having this issue in the future.

    Best Regards


    you can follow the URL and add your self as sysadmin.
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Tuesday, June 28, 2011 12:11 PM
  • Hi, I've tried following the URL but SQLCMD wouldn't work due to the same issue. Infact, after a restart SQL Server fails to startup it's service. From the installation logs i see that the installer tried to create the TEMPDB and a few other files on the E:\ drive (which is a CD/DVD) drive, but i had kept everything to default (i.e. the C:\ drive). Has anyone else witnessed a similar issue ?

    Thursday, June 30, 2011 2:02 PM
  • The complete issue was due to a Windows OS Permissions issue. I wish the logs were more helpful. For resolution see

    http://support.microsoft.com/kb/2000257

    • Marked as answer by dct374 Friday, July 1, 2011 2:43 PM
    Friday, July 1, 2011 2:43 PM