Solved

SQL 2012 - Database Move to new server - Unknown SQL User password

Posted on 2016-09-18
10
49 Views
Last Modified: 2016-10-23
Hi all,
Ive been asked to move a DB to a new server.

Current and new SQL servers are running Server2012R2, both members of the domain, and both have identical versions SQL versions 11.0.5613.  

The DB has 2 x local SQL users - And you guessed it,  no one knows the passwords for these accounts.

Is there any way of showing these passwords, or exporting\importing the SQL users from one SQL Server to another?.

Many thanks,
String
0
Comment
Question by:TreadStone_IT
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
You can take backup and restore the back somewhere else. There you will get everything including users.

There is not way you can view password of other people.
0
 

Author Comment

by:TreadStone_IT
Comment Utility
Hi Pawan, Thanks for taking time to comment on my problem.

I have tried backup and restore of database on new server however that leaves orphan'ed users in the DB.
ie
DB references a SQL user that new server doesnt know about.
Any tips?
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Boss

Here is what you need..

Resolve an Orphaned User
In the master database, use the CREATE LOGIN statement with the SID option to recreate a missing login, providing the SID of the database user obtained in the previous section:
CREATE LOGIN <login_name>  
WITH PASSWORD = '<use_a_strong_password_here>',  
SID = <SID>;  

To map an orphaned user to a login which already exists in master, execute the ALTER USER statement in the user database, specifying the login name.
ALTER USER <user_name> WITH Login = <login_name>;  

When you recreate a missing login, the user can access the database using the password provided. Then the user can alter the password of the login account by using the ALTER LOGIN statement.
ALTER LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>';  

System_CAPS_ICON_important.jpg Important

Any login can change it's own password. Only logins with the ALTER ANY LOGIN permission can change the password of another user's login. However, only members of the sysadmin role can modify passwords of sysadmin role members.

https://msdn.microsoft.com/en-IN/library/ms175475.aspx

Pls let me know if you need more help on this.
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Sample Code..

--

--

USE DBNAME;
GO

CREATE LOGIN sqlUser
	WITH PASSWORD = 'P@ssword', DEFAULT_DATABASE = DBNAME;
GO

CREATE USER sqlUser
FOR LOGIN sqlUser;
GO

DROP LOGIN sqlUser;


CREATE LOGIN sqlUser
	WITH PASSWORD = 'P@ssword', DEFAULT_DATABASE = DBNAME;
GO

--

Open in new window


--
0
 
LVL 13

Expert Comment

by:Megan Brooks
Comment Utility
You should be able to back up and restore the master database along with the application database(s), which is where all those logins and passwords are stored. It has been many years since I last did that, and I don't know what limitations there may be today, but as long as the versions are exactly the same it should be possible. It might be good to keep the file paths the same as well -- something is trying to come back to me about that but it hasn't so far.

When I did this, when I reached the 'restore' part I followed the steps from a TechNet article like this one. Here is another take on the subject.

You could still have orphaned users in the application databases afterward, but you can reconnect them to their logins using sp_change_users_login. I normally use the 'update_one' option, but auto_fix might be useful in this situation.

I believe I have also, in the distant past, managed to restore msdb, so as to not have to set up all the jobs again. model can be restored too. I used an article like this as a guide.

Have you considered virtualization? :-)
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 13

Expert Comment

by:Megan Brooks
Comment Utility
I am assuming, by the way, that you aren't using contained databases.
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
Comment Utility
Use this code to generate the create login statements with password information from the source server. You copy the output of this script and you execute it in the new server:
-- ##### The script will create 2 sps in master db and then will execute the sp_help_revlogin procedure to generate scripts for all logins
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO
 
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname
 
IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR


      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
 
        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
 
            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

EXECUTE sp_help_revlogin

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Any feedback about this question? Please check the answers.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
The DB has 2 x local SQL users - And you guessed it,  no one knows the passwords for these accounts.
Are the users being used from an application? If so the application has those passwords stored and you just need to find if it's encrypted or not.
0
 

Author Closing Comment

by:TreadStone_IT
Comment Utility
Hi all,
This migration all went well.
I used Zberteoc's script which ran well and achieved the result.  I did research what the script did and how to use it before running it.

More background info referenced here.
Method 1
https://support.microsoft.com/en-au/kb/246133

Many thanks to all who contributed.
String
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now