Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4

    Unanswered: Migrating logins/passwords from ASE12.5 to ASE15.5

    While migrating several applications from ASE12.5 to ASE15.5 I am encountering a problem with paswords. When I use bcp to transfer logins from one server to the other the passwords do not seem to work anymore.

    The length of passwordstrings in 12.5 is short than in 15.5. After resetting the password to exactly the same value as it was in the old server it works again, but the passwordstring is now longer.

    How can I tackle this problem? I can recreate/reset logins with known passwords, but that doesn't work for the dozens of user logins with unknown passwords. And resetting them to something new is not too user friendly.
    Ideas are greatly appreciated...
    I'm not crazy, I'm an aeroplane!

  2. #2
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Hi,
    Can you show us the bcp comand you are using, both "out" and "in".

    If you are reseting the password to the same on the new installation and it works, does not indicate to be "minimum password length" issue. Your bcp might be it...

    "If data in varchar and varbinary fields is longer than the length you specify for copy out, bcp silently truncates the data in the file at the specified length."
    I assume you have the same charset on both Instances.

    Hope it helps

  3. #3
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    I've used the following steps:
    bcp master..syslogins out syslogins.bcp -Soldserver -Usa -Ppassword -c -t*#* -r\n

    In the new server:
    use tempdb
    go
    CREATE TABLE syslogins125
    (
    suid int NOT NULL,
    status smallint NOT NULL,
    accdate datetime NOT NULL,
    totcpu int NOT NULL,
    totio int NOT NULL,
    spacelimit int NOT NULL,
    timelimit int NOT NULL,
    resultlimit int NOT NULL,
    dbname sysname NULL,
    name sysname NOT NULL,
    password varbinary(30) NULL,
    language varchar(30) NULL,
    pwdate datetime NULL,
    audflags int NULL,
    fullname varchar(30) NULL,
    srvname varchar(30) NULL,
    logincount smallint NULL,
    procid int NULL
    )
    go

    bcp tempdb..syslogins125 in syslogins.bcp -Snewserver -Usa -Ppassword -c -t*#* -r\n

    alter table syslogins125
    add lastlogindate datetime null
    , crdate datetime null
    , locksuid int null
    , lockreason int null
    , lockdate datetime null
    , newsuid int identity
    go

    declare @startid int

    select @startid = max(suid) from master..syslogins

    update syslogins125
    set suid = @startid + newsuid
    go

    alter table syslogins125 drop newsuid
    go

    exec sp_configure 'allow updates to system tables', 1
    go

    insert into master..syslogins
    select * from syslogins125
    go

    exec sp_configure 'allow updates to system tables', 0
    go
    I'm not crazy, I'm an aeroplane!

  4. #4
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Hi again,

    Based on your example, i had some troubles bcp in data to that temp table.

    I believe at least you password field is smaller ( you have varbinary (30), but should be varbinary(128) )

    Code:
    CREATE TABLE tempdb..syslogins125
    (
     suid           int                    NOT NULL,
     status         smallint               NOT NULL,
     accdate        datetime               NOT NULL,
     totcpu         int                    NOT NULL,
     totio          int                    NOT NULL,
     spacelimit     int                    NOT NULL,
     timelimit      int                    NOT NULL,
     resultlimit    int                    NOT NULL,
     dbname         sysname                NULL,
     name           sysname                NOT NULL,
     password       varbinary(128)         NULL,
     language       varchar(30)            NULL,
     pwdate         datetime               NULL,
     audflags       int                    NULL,
     fullname       varchar(30)            NULL,
     srvname        varchar(30)            NULL,
     logincount     smallint               NULL,
     procid         int                    NULL
    )
    
    alter table tempdb..syslogisn125 
    add
    
     lastlogindate  datetime               NULL,
     crdate         datetime               NULL,
     locksuid       int                    NULL,
     lockreason     int                    NULL,
     lockdate       datetime               NULL
    Then after loading if i compare the 2 tables by their password to check for matches i get all the rows.

    select count(*) from tempdb..syslogins125 s, master..syslogins l
    where l.password = s.password


    Try again with the password has varbinary 128, it could have been "truncated".

    Other than that, assuming your charset is the same from origin and destination, i dont see anything wrong with your method.

  5. #5
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    In ASE12.5 (the old server) the password column has the datatype varbinary(30), hence the similar field length in the bcp-in table.
    If I alter the bcp table to varbinary(128) I get no errors gain, but I still cannot login with the known passwords.

    select * from master..syslogins on the new ASE15.5-server after the import shows a few original logins that were already present and the newly imported ones.
    The old records show a longer password-string than the imported ones.

    The password string on the old server for password Pwd01 is different than the pwd string for the same password on the new server, of a newly created login (or a reset password). Apparently there is a difference in the way passwords are encrypted?
    I'm not crazy, I'm an aeroplane!

  6. #6
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Hmm.... I wouldn't think so, can you test on a 15.0.X version just the logins, or create one login (TESTE123) in the 12 version and load it onto the 15.0.x ?
    Athough I'm just fishing with the ^^.... (behaviour should be the same on 15.0 and 15.5).

    One of the new features of the 15.5 is the ’FIPS login password encryption’, but on a fresh installation like yours this should be off.

    Can you check the charset? sp_helpsort

    Thanks

  7. #7
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Unfortunately I only have 12.5 and 15.5 environments at my disposal, no 15.0.
    FIPS Login password encryption is off, and character sets match. The only difference between the old and the new server is the OS. I am migrating from Windows, HPUX and Tru64 to Redhat.

    I might have to log a call at Sybase for this.
    I'm not crazy, I'm an aeroplane!

  8. #8
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Hi,

    Okay so one last try before you open the case.
    Create a connection from the new instance to the old. Create a proxy table to point to the "source" syslogins, and do a select into your temporary table there. It might be OS related, changing the content or your bcp files. Just to idenitfy if is a Sybase issue or maybe a OS issue.

  9. #9
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    Good idea, I will try that first thing Monday. Stay tuned, I'll get back to you.
    I'm not crazy, I'm an aeroplane!

  10. #10
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    HAH! Using the proxytable did the trick!
    Thank you Catarrunas, this saves me a lot of hassle with users.
    I'm not crazy, I'm an aeroplane!

  11. #11
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Cool!
    Most likely you we copying the bcp files, without converting them from Win to Unix files.
    You can do the conversion using notepad ++. Edit -> EOL Conversion -> Unix.....
    And then ftp them into Unix and try the load. Should also work.

    Im glad you are set.

Posting Permissions

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