Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2006
    Posts
    4

    Unanswered: Urgent!: Upgade MSDE 2000 to Sql Server Express - Authentication issue

    I am trying to do a unattended upgrade of an MSDE 2000 named instance, say MyInstance, to a SQl Server Express Advanced edition. The set up is Mixed Mode Authentication. ( I can't use Windows Authentication for business reasons).
    The error I get is

    SQL Server Setup Cannot Upgrade the specified instance by using SQL Server
    Creditial. You must use Windows Authentication creditial for the upgrade.

    Details are as follows:
    OS: Windows XP Prof (SP2).
    Original install: MSDE 2000
    Instance name: MyInstance
    Authentication: SQL
    Registry Entry: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MyInstance\MSSQLServer
    Key LoginMode has a value of 2 ( corresponding to Mixed Mode Auth)

    Upgrade attempted to: SQL Server Express Adv.
    Command Line: start /wait setup.exe /qn LOGNAME=C:\setup.cab /settings E:\Sql2005\SqlTemplate.ini

    SqlTemplate.ini Contents:
    [OPTIONS]
    INSTANCENAME=MyInstance
    SECURITYMODE=SQL
    UPGRADE=SQL_Engine
    UPGRADEUSER=sa
    UPGRADEPWD=My$Password

    The error I get in Summary.txt:
    Machine : xxxxxXP
    Product : SQL Server Database Services
    Error : SQL Server Setup cannot upgrade the specified instance by using SQL Server credentials. You must use Windows Authentication credentials for the upgrade.


    Can't I use SQL Authentication? I could not find a definitive answer on this one. Here's one thread that seem to indicate the same issue

    http://groups.google.com/group/micro...128b447585f29d

    My questions:
    - Can I use SQL Authentication?
    - Where am I going wrong?
    - I know the error message says to use Windows Authentication but I don't understand why I am I forced into using that.

    Any insights\opinions will be highly appreciated.

    Best
    LW.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    why not use windows auth to do the setup and then switch to mixed mode afterwards? it should just use the credentials you are logged into the computer to run the setup, right?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The actual installation (upgrade) of SQL 2005 must be done using Windows authentication. Once you've got SQL 2005 running you can still re-enable and use SQL authentication, but the upgrade must be done using Windows credentials.

    -PatP

  4. #4
    Join Date
    Aug 2006
    Posts
    4
    @Thrasymachus, @PatP,

    Thanks for your replies. Currently, all our installs at clent sites are MSDE with Sql Authentication. We are planning on moving to Express and I was trying things out to see how smooth or not the upgrade would be.

    Looks like I have to go the Windows Authentication way as you suggested. This would mean I have to flip the existing installs to Windows Authentication temporarily, do upgrade and then flipback.

    Thanks once again.

    Best.
    LW

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You don't need to set Windows Authentication only before the upgrade, you just need to use Windows Authenticated credentials to run the upgrade. I don't remember if the upgrade process sets the upgraded server to use Windows Authentication only (it probably does to meet the security requirements), but I know that you can switch back to mixed authentication afterwards regardless of what the upgrade process does.

    -PatP

  6. #6
    Join Date
    Aug 2006
    Posts
    4
    Pat..
    I will try what you said. Here's what I tried and it did not work.

    In my original MSDE install, i had set it to SQL authentication and later also disabled Builtin Admin permissions using sp_revoke.... To try the way you guys suggested, i logged in as an administrator into the machine (not sql server), logged into sql server using sql authentication and did a sp_grant for built in admins so that I can use Windows Auth. Then tried running the upgrade again. It still fails with credentials related message. However, Sql management studio is able to connect to it. Have a feeling that I am missing some command line argument but am not able to nail it.

    This is how I tried.
    start /wait setup.exe /qn LOGNAME=C:\setup.cab /settings E:\Sql2005\SqlTemplate.ini

    SqlTemplate.ini Contents:
    [OPTIONS]
    INSTANCENAME=MyInstance
    UPGRADE=SQL_Engine


    Any thoughts?

  7. #7
    Join Date
    Aug 2006
    Posts
    4
    Just FYI,
    i was able to get it working by recreating Builtin\Admin and granting sysadmin rights. Not the way I would have liked it, but now that this is working, have to check for better ways to do it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •