Monday, April 16, 2012

Transfer the logins and the passwords between instances of Microsoft SQL Server 2005 on different servers

INTRODUCTION
This article describes how to transfer the logins and the passwords between instances of Microsoft SQL Server 2005 on different servers.

For more information about how to transfer the logins and the passwords between instances of other versions of SQL Server, click the following article number to view the article in the Microsoft Knowledge Base:
246133 (http://support.microsoft.com/kb/246133/) How to transfer logins and passwords between instances of SQL Server.

MORE INFORMATION
In this article, server A and server B are different servers. Additionally, both server A and server B are running SQL Server 2005.

After you move a database from the instance of SQL Server on server A to the instance of SQL Server on server B, the users may not be able to log in to the database on server B. Additionally, the users may receive the following error message:
Login failed for user 'MyUser'. (Microsoft SQL Server, Error: 18456)This problem occurs because you did not transfer the logins and the passwords from the instance of SQL Server on server A to the instance of SQL Server on server B.

To transfer the logins and the passwords from the instance of SQL Server on server A to the instance of SQL Server on server B, follow these steps:

1. On server A, start SQL Server Management Studio, and then connect to the instance of SQL Server from which you moved the database. Open a new Query Editor window, and then run the following script.

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.has access, 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

2. Note This script creates two stored procedures in the master database. The two stored procedures are named the sp_hexadecimal stored procedure and the sp_help_revlogin stored procedure.

3. Run the following statement.

EXEC sp_help_revlogin

The output script that is generated by the sp_help_revlogin stored procedure is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.

4. On server B, start SQL Server Management Studio, and then connect to the instance of SQL Server to which you moved the database.

Important Before you go to step 5, review the information in the "Remarks" section.

5. Open a new Query Editor window, and then run the output script that is generated in step 3.

Remarks
Review the following information before you run the output script on the instance on server B:
* Review the output script carefully. If server A and server B are in different domains, you have to modify the output script. Then, you have to replace the original domain name with the new domain name in the CREATE LOGIN statements. The integrated logins that are granted access in the new domain do not have the same SID as the logins in the original domain. Therefore, the users are orphaned from these logins. For more information about how to resolve these orphaned users, click the following article number to view the article in the Microsoft Knowledge Base:
240872 (http://support.microsoft.com/kb/240872/) How to resolve permission issues when you move a database between servers that are running SQL Server If server A and server B are in the same domain, the same SID is used. Therefore, the users are not likely to be orphaned.

* In the output script, the logins are created by using the encrypted password. This is because of the HASHED argument in the CREATE LOGIN statement. This argument specifies that the password that is entered after the PASSWORD argument is already hashed.

* By default, only a member of the sysadmin fixed server role can run a SELECT statement from the sys.server_principals view. Unless a member of the sysadmin fixed server role grants the necessary permissions to the users, the users cannot create or run the output script.

* The steps in this article do not transfer the default database information for a particular login. This is because the default database may not always exist on server B. To define the default database for a login, use the ALTER LOGIN statement by passing in the login name and the default database as arguments.

* The sort order of server A may be case insensitive, and the sort order of server B may be case sensitive. In this case, the users must type all the letters in the passwords as uppercase letters after you transfer the logins and the passwords to the instance on server B.

Alternatively, the sort order of server A may be case sensitive, and the sort order of server B may be case insensitive. In this case, the users cannot log in by using the logins and the passwords that you transfer to the instance on server B unless one of the following conditions is true:
# The original passwords contain no letters.
# All the letters in the original passwords are uppercase letters.
The sort order of both server A and server B may be case sensitive, or the sort order of both server A and server B may be case insensitive. In these cases, the users do not experience a problem.

A login that already is in the instance on server B may have a name that is the same as a name in the output script. In this case, you receive the following error message when you run the output script on the instance on server B:
Msg 15025, Level 16, State 1, Line 1
The server principal 'MyLogin' already exists.Similarly, a login that already is in the instance on server B may have a SID that is the same as a SID in the output script. In this case, you receive the following error message when you run the output script on the instance on server B:
Msg 15433, Level 16, State 1, Line 1
Supplied parameter sid is in use.Therefore, you must do the following:
1. Review the output script carefully.
2. Examine the contents of the sys.server_principals view in the instance on server B.
3. Address these error messages accordingly.

In SQL Server 2005, the SID for a login is used as the basis for implementing database-level access. A login may have two different SIDs in two different databases on a server. In this case, the login can only access the database that has the SID that matches the SID in the sys.server_principals view. This problem may occur if the two databases are consolidated from two different servers. To resolve this problem, manually remove the login from the database that has a SID mismatch by using the DROP USER statement. Then, add the login again by using the CREATE USER statement.
Ref:
http://support.microsoft.com/kb/918992/

Thursday, April 12, 2012

Technologies that will be discontinued in the NAV 7

In Microsoft Dynamics NAV “7", This release will deliver significant enhancements and new capabilities, such as the new Web and SharePoint clients.
To enable these transformations, we will be optimizing selected technology components, or replacing with more modern or more standardized alternatives.
The list below outlines key technologies that will be discontinued in the release

Updated technologies highlighted in this document
1 C/FRONT and C/FRONT .NET (Remove from Microsoft Dynamics NAV “7”):
Recommended technology to be used in Microsoft Dynamics NAV “7”
For reading and writing data from external programs and systems, use Microsoft
Dynamics NAV Web Services:
• Simple Object Access Protocol (SOAP) Web Services (originally introduced in
Microsoft Dynamics NAV 2009).
• New Microsoft Dynamics NAV Queries.
• New “OData” Web Services.

2 Microsoft Dynamics Server-side COM components (Remove from Microsoft Dynamics NAV “7”):
Recommended technology to be used in Microsoft Dynamics NAV “7”
Change or redevelop your COM components to .NET. Utilize the standard .NET library or your own.NET types directly from C/AL code using the .NET Interoperability feature Introduced in Microsoft Dynamics NAV 2009 R2,or the new enhanced .NET Interoperability features to access the .NET components. We will support
.NET 4.0 and below. A .NET DLL compiled with Any CPU can run on both Server and Client Side. A .NET DLL compiled towards a specific target must be 64-bit for the Server and 32-bit for the Client. You can continue to use COM components on the Windows Client but it will not be possible to deploy server-side COM components on a Worker Role in Windows Azure.
• Alignment with .NET development process, enabling developers to utilize most of the .NET standard library directly from C/AL.
• Free reuse of a vast library of functionality relevant for Business Applications from the .NET Framework. Furthermore, as the .NET Framework is part of the Microsoft Dynamics NAV install, there is no need to install additional external components when using it.
• Simplified component management using .NET Interop and the .NET Framework.
• You can still develop your own .NET components for tasks that are better suited for .NET development.

3 Microsoft Dynamics NAV Application Server (NAS) (Remove from Microsoft Dynamics NAV “7”):
Recommended technology to be used in Microsoft Dynamics NAV “7”
For “passive” service scenarios – where Microsoft Dynamics NAV acts upon incoming requests - use Microsoft Dynamics NAV Web Services.
For asynchronous/background execution scenarios, use the new Background sessions.
For “active” scenarios - where a headless session actively manages what needs to be done - utilize the new “NAS on NAV Service Tier”.
• Ability to leverage the Microsoft Dynamics NAV Service Tier.
• More stable NAS installations that are easier to maintain using Windows Service Recovery Management and the Event Log. In addition, Microsoft Dynamics NAV “7” is PowerShell scriptable.
• Network Printing Support can be utilized from the Background sessions.

4 Dataport Object Type (Remove from Microsoft Dynamics NAV “7”):
Recommended technology to be used in Microsoft Dynamics NAV “7”For Fixed and Variable Text import and export, and XML import and export, use the new XMLport Object Type. The XMLport Object Type is enhanced in a number of ways, and now also supports:
• Unicode.
• Large files.
• Auto-Save/Update/Replace.
• Licensing.

5 Classic Runtime Stack (Remove from Microsoft Dynamics NAV “7”):
Recommended technology to be used in Microsoft Dynamics NAV “7”
Forms and any other means of executing on the Classic runtime stack are removed,
and all runtime execution is on the three-tier runtime stack. As a result, all classic runtime is replaced by execution on the NST (NAV Service Tier). There are several ways to invoke the NST runtime:
• Pages in the RTC.
• SharePoint client.
• Web client.
• Triggers on various events from XML Ports.
• NAS invoking AL code.
• Web services. Essentially, any runtime component will go through the NST rather than directly to the database, and all C/AL code will execute on the NST.

6 Classic database (Remove from Microsoft Dynamics NAV “7”):
Recommended technology to be used in Microsoft Dynamics NAV “7”
Replaced by SQL server only support.
• Delivering performance benefits and meeting the current and future needs.
• Enabling the new Query object for fast data access.
• Alignment with Microsoft SQL Server strategy.

7 Classic/Hybrid reports (Remove from Microsoft Dynamics NAV “7”):
Recommended technology to be used in Microsoft Dynamics NAV “7”
Replaced by SQL Server Reporting Services (SSRS) based Report Definition Language Client-side (RDLC) reports. Reports are RDLC 2008 based, taking advantage of the SSRS report definition and rendering engine.
• Stronger reporting capabilities as a response to customer needs
• Enabling new report design capabilities for creating professional and elegant looking reports in Microsoft Dynamics NAV.

8 Classic Debugger (Remove from Microsoft Dynamics NAV “7”):
Recommended technology to be used in Microsoft Dynamics NAV “7”
Replaced by the debugger in the Role Tailored Client.
• The debugger UI runs on the familiar RoleTailored client.
• A more extensive debugger with added capabilities.
• You can now debug RoleTailored client sessions, Microsoft Dynamics NAV Portal Framework for Microsoft SharePoint 2010 sessions, Odata & SOAP web services sessions, and NAS services sessions


9 32-bit server (Remove from Microsoft Dynamics NAV “7”);
Recommended technology to be used in Microsoft Dynamics NAV “7”
The NST in Microsoft Dynamics NAV “7” will only support 64-bit execution, so a
64-bit Windows OS is required to run the NST.


Please note that this communication should not be considered a complete listing of technology changes planned for Microsoft Dynamics NAV “7”.