Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2012
    Posts
    7

    Unanswered: ODBC/DSN connection problems to sql server 7

    A company pulled me in and wanted me to help with their sql server after a raid failure.
    They retrieved the data off the drive and re installed on a virtual server.
    They hired another company to install sql 7 from scratch and import the data.
    The server hosts their website which ties to the SQL server and 2 programs which store data on the sql server.

    Now is the part i am confused on and am sure you guys will know it.
    I think everything is setup to work through odbc connections. (dsn)

    The website is showing this error

    Microsoft OLE DB Provider for ODBC Drivers error '80040e4d'
    [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'bhPress_user'.
    /connect.asp, line 15

    On the server there are system dsn's and file dsn's in the odbc list.

    Since the company installed SQL 7 from scratch i believe sql lost all the login information? Am I on the right path?

    How do I go about setting up all these connections back up? I can provide any code you need.

    All help is greatly appreciated and thanks for your time guys.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    You are very much on the correct path.

    The easiest way to get the login information back is to restore the master database from the old machine. A number of things would need to be made sure of beforehand. Mainly are all of the paths to the files the same as they were before. If not, you could have all sorts of fun stopping and restarting in single user mode to fix the system databases (model and tempdb will be the most important).

    Alternatively, you could try to redefine the logins. I would only do that if there were only a handful of them, though. Say 5 - 10.

    You may also want to restore the msdb database, just to make sure that you get any DTS packages, or SQL Agent jobs they may have defined over the years.

    SQL 7.0? Really? Wow. just...wow.

  3. #3
    Join Date
    Jun 2012
    Posts
    7

    Thank you

    Thanks for the reply. I will see if i can restore those databases.

    I really do appreciate the help.

    I know enough about sql on a querying side but not on the security side. What all do i need to do to redefine the logins?
    Something like : http://support.microsoft.com/kb/274188#appliesto


    Thank you again so much!
    Last edited by rohwer3; 02-07-13 at 18:34.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    To manually create the logins, you would have to first create the login (with the password). Then you can reset the SID with the procedure you found above. The real trick is tracking down all the passwords, which is why it is generally easier to restore the master database.

  5. #5
    Join Date
    Jun 2012
    Posts
    7

    bad news

    After working on it this weekend O have discovered the Master DB is corrupt.

    I have tried a bunch of sql recovery programs and the only one that data is shown with is SQL Database Recovery Software - Repair Corrupt & Damaged SQL Databases.

    when i get the the master.mdf open i go to sysxlogins and the names are there but the sid and passwords are displayed as binary data.

    Are my only two options manually adding the users in and educated guess the password? Or try and see if the client has an archived backup somewhere and restore that and script out passwords(he is looking for a backup).

    Nothing like working on a virtualized windows 4.0 system! Sooo far behind the times!

    Again I do appreciate any advice and help.

    - Jarrod

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    No, you can use the hashed passwords. the sp_addlogin procedure will take the following parameters:

    Code:
    sp_addlogin @name = loginname,
      @password = 0x000, -- whatever the hashed password is
      @dbname = default database name,
      @language = usually us_english,
      @sid = sid of the login
      @skip_encryption = 'skip_encryption'
    EDIT: Note, this is largely from memory of SQL 7.0, which was 10 years ago for me. The names of the parameters may be slightly off, as may be the order. Some experimentation will be required.

Posting Permissions

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