Migrate/Transfer SQL Server 2008/2005/2000/7 Logins to SQL Server 2008
To my knowledge, Microsoft has not yet updated their Knowledge Base to inform administrators how to migrate SQL Server logins to SQL Server 2008. The good news is that the SQL Server 2005 login migration knowledge base article applies to SQL Server 2008 as well. Below I've included a compilation of all of the migration procedures between various versions of SQL Server.
Migration is very easy -- basically, a stored procedure named sp_help_revlogin is created and executed on the source server. The resulting output is then copied and executed on the destination server.
Detailed instructions can be found at Microsoft KB Article 918992 and Microsoft KB Article 246133. Oh, also, Dell has a marvelous 54-page SQL Server 2000 to 2008 Migration Guide. It actually outlines, in an easy-to-read style, a lot of the best practices I learned about while studying for my MCITP certification.
Code For Login Migrations Between SQL Server 7 and SQL Server 2000
----- Begin Script, Create sp_help_revlogin procedure -----
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(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
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 @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
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 ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE BEGIN -- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
----- End Script -----Code For Login Migrations From SQL Server 7/2000 to SQL Server 2005/2008
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(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
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_2000_to_2005') IS NOT NULL
DROP PROCEDURE sp_help_revlogin_2000_to_2005
GO
CREATE PROCEDURE sp_help_revlogin_2000_to_2005
@login_name sysname = NULL,
@include_db bit = 0,
@include_role bit = 0
AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @dfltdb varchar (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR STATIC FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
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 ''
PRINT ''
PRINT ''
PRINT '/***** CREATE LOGINS *****/'
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = '' --'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE
BEGIN -- NT login has access
SET @tmpstr = 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ''' + @name + ''')'
PRINT @tmpstr
SET @tmpstr = CHAR(9) + 'CREATE LOGIN [' + @name + '] FROM WINDOWS'
PRINT @tmpstr
END
END
ELSE
BEGIN -- SQL Server authentication
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=' + @txtpwd + ' HASHED'
END
ELSE
BEGIN -- Null password
SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD='''''
END
SET @tmpstr = @tmpstr + ', CHECK_POLICY=OFF, SID=' + @SID_string
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
IF @include_db = 1
BEGIN
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** SET DEFAULT DATABASES *****/'
FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
SET @tmpstr = 'ALTER LOGIN [' + @name + '] WITH DEFAULT_DATABASE=[' + @dfltdb + ']'
PRINT @tmpstr
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
END
IF @include_role = 1
BEGIN
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** SET SERVER ROLES *****/'
FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF @xstatus &16 = 16 -- sysadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''sysadmin'''
PRINT @tmpstr
END
IF @xstatus &32 = 32 -- securityadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''securityadmin'''
PRINT @tmpstr
END
IF @xstatus &64 = 64 -- serveradmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''serveradmin'''
PRINT @tmpstr
END
IF @xstatus &128 = 128 -- setupadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''setupadmin'''
PRINT @tmpstr
END
IF @xstatus &256 = 256 --processadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''processadmin'''
PRINT @tmpstr
END
IF @xstatus &512 = 512 -- diskadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''diskadmin'''
PRINT @tmpstr
END
IF @xstatus &1024 = 1024 -- dbcreator
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''dbcreator'''
PRINT @tmpstr
END
IF @xstatus &4096 = 4096 -- bulkadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''bulkadmin'''
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1
GOCode For Login Migrations From SQL Server 2005/2008 to SQL Server 2005/2008
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
GOMmm, hella swole code.
PowerShell 1.0: Backup and Restore a Database Between Two SQL 2005 Servers
My technical partner-in-crime, Brandon, created this PowerShell script last year in an effort to help him understand PowerShell a bit more. He noted that while it may not all be best practices SQL-wise, he tried his best to use as much PowerShell as possible.
#Powershell to backup and restore a SQL database from server1 to server2.
#2008, Brandon Abshire / netnerds.net
#load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
# create source and destination sql servers
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'server1'
$d = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'server2'
# set backup directory, dbname and grab date
$bkdir = 'e:\'
$remoteDir = 'e$\'
$dbname = 'databasename'
$dt = get-date -format yyyyMMddHHmmss
#create backup object and set properties
$dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
$dbbk.Action = 0
$dbbk.BackupSetDescription = "Backup of " + $dbname
$dbbk.BackupSetName = $dbname + " Backup"
$dbbk.Database = $dbname
$dbbk.Devices.AddDevice($bkdir + "\" + $dbname + $dt + ".bak", 2)
$dbbk.MediaDescription = "Disk"
$dbbk.Incremental = $false
#Backup the database
$dbbk.SqlBackup($s)
#create restore object and set properties.
$dbres = New-Object ('Microsoft.SqlServer.Management.Smo.Restore')
$dbres.Database = $dbname
$dbres.Action = 0
$dbres.ReplaceDatabase = $true
#Add the backup that is on the remote server by whatever method you wish.
#You can copy it locally first. I just used named pipes to grab it off the network, since it is a fast enough connection.
$dbres.Devices.AddDevice("\\" + $s + "\" + $remoteDir + $dbname + $dt + ".bak", 2)
$dbres.NoRecovery = $false
#Without the KillAll, my restore got stuck in "Restoring..." forever.
$d.KillAllProcesses('databasename')
#restore the database.
$dbres.SqlRestore($d)
#The database is now restored from one server to another.
#We must re-map the mappings on the database for my user, or login will fail.
#Set db to the specific database we want on the destination server.
$db = $d.databases["databasename"]
#Set newUser to the user we want to give permissions to and drop it.
#This user was restored from the other database and doesn't match up
#to the one on the destination SQL server.
#sp_change_users_login 'autofix','userlogin' could also be used but I
#wanted to practice PS moreso than SQL
$newuser = $db.users["username"]
$newuser.drop()
#Create a new user. Set the parent to the database we restored.
$newuser = New-Object ('Microsoft.SqlServer.Management.Smo.User')
$newuser.parent = $db
#Set the username and login name.
$newuser.name = "My User"
$newuser.login = "username"
#Set the default Schema, create the user and add the appropriate roles.
$newuser.defaultSchema = "dbo"
$newuser.Create()
$newuser.AddToRole("db_datareader")
$newuser.AddToRole("db_datawriter")Credit goes out to the Allen White, MVP of the MSDN Forums for a PowerShell/SQL quickstart.
SharePoint Server 2007 SP1: Incorrect syntax near ‘COLLATE’ When Joining a Farm
While attempting to add a new Indexing Server to my firm's SharePoint farm using the SharePoint Products and Technologies Configuration Wizard, I ran into the following error when clicking Retrieve Database Names:
Incorrect syntax near 'COLLATE'
************** Exception Text **************
System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'COLLATE'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.SharePoint.PostSetupConfiguration.SqlSession.ExecuteNonQuery(SqlCommand command)
at Microsoft.SharePoint.PostSetupConfiguration.SqlServerHelper.DatabaseTableWithColumnExists(String table, String column)
at Microsoft.SharePoint.PostSetupConfiguration.SqlServerHelper.GetV3WSSConfigurationDatabases()
at Microsoft.SharePoint.PostSetupConfiguration.ConnectConfigurationDbForm.GetDatabasesButtonClickEventHandler(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)The SharePoint_Config's database and set to collation is set to Microsoft's recommended collation for a SharePoint farm: Latin1_General_CI_AS_KS_WS while SQL Server 2005's default collation on the master database is set to SQL_Latin1_General_CP1_CI_AS. For whatever reason, this is causing a very time consuming issue on our SharePoint farm. With the help of my SharePoint Consultant friend, Trevor, I was able to resolve it by doing the following:
- Ensuring the newly installed SharePoint server's service pack matched that of the farm. Initially, the RTM was installed on the server but the farm was running SharePoint Server 2007 SP1.
- Typing in the Sharepoint config's database name (SharePoint_Config) manually instead of relying on the "Retrieve Database Name" button to populate the drop-down list.
I know it's not a solution so much as it is a workaround but after three days of failed configuration attempts, I really just wanted it to work and that did the trick.
Shared Services Administration: Error: Access is Denied
This has come up a few times for me, so I'm documenting this mostly for myself. Sometimes, clicking on the SharedServices1 link results in:
Error: Access Denied
Current User
You are currently signed in as: MOSSERVER\AdminLogin
Surprisingly, I was using a domain admin account and still ran into this error. According to the MSDN forums, the resolution is as follows:
Central Administration >> Application Management >> Policy for Web Application >> Add Users >> Select the Web Application (All Zones) >> Web Application >> (Add the user) >> Full Control >> Finish
Additionally, you may also need to perform the following once you have permissions: SharedServices1 >> "User Profiles and My Sites" >> "Personalization Services Permissions" (Thanks, James!)
Efficiently Compare Two SQL Server Tables Using Mostly VBScript
Recently, a friend of mine asked to write a script to compare two SQL Server tables using mostly VBScript. The very thought made me shudder; I wanted desperately to compare the tables in SQL Server but ultimately, the company's requirements prevented us from doing so. Here were the stipulations:
- First, the script should be as resuable as possible. Many tables will be compared.
- The column names are unknown -- only the database name and table were consistent.
- The SQL Servers were 2005 and both located on the same domain.
- Windows Authentication must be used.
- SSIS/DTS cannot be used.
- Files can be written to the hard drive, but only as a last resort.
- OPENROWSET/OPENDATASOURCE/Linked Servers cannot be used.
- The script's output must be either TRUE or FALSE. TRUE if the tables matched, FALSE if they didn't.
- IDENTITY columns must be ignored.
I told him I'd be back in 20 minutes as my brain figured it out. This is what it came up with:
- ADO's recordset getstring() will be used for direct comparisons. No variables will need to be assigned, no data will need to be written.
- The INFORMATION_SCHEMA will be queried to discover column names. Column names will be reused by both SELECT and ORDER BY.
- ORDER BY will be used to ensure the data is returned in the same order on both tables.
- COLUMNPROPERTY will be used to check if the column is an identity (I know excluding the ID means that the table may not be exactly a match, but it's what was requested, so I obliged.)
strServer1 = "server1"
strServer2 = "server2"
strDatabaseName = "mydatabase"
strTableName = "mytable"
CALL CompareTables(strServer1,strServer2,strDatabaseName,strTableName)
SUB CompareTables(server1,server2,databaseName,tableName)
'This is a SQL Server 2005 Connection String with Windows Authentication. Change as necessary.
connString1 = "Provider=SQLNCLI;Server=" & server1 & ";Database=" & databaseName & ";Integrated Security=SSPI;"
connString2 = "Provider=SQLNCLI;Server=" & server2 & ";Database=" & databaseName & ";Integrated Security=SSPI;"
'Since this is a template script, we don't know what the columns are so we'll use the INFORMATION_SCHEMA to figure it out.
'Not only will this be useful in the SELECT statement (SELECT col1, col2, col3) but also is necessary for the ORDER BY.
'Because we'll be using a text comparison, columns and order by must be in the same exact order on both server1 and server2
strSQLColumns = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" & tableName & "' AND (COLUMNPROPERTY(OBJECT_ID('" & tableName & "'),column_name,'IsIdentity')) != 1 ORDER BY ORDINAL_POSITION ASC"
SET rsColumns = CreateObject("adodb.recordset")
rsColumns.Open strSQLColumns, connString1, 1,1
IF rsColumns.eof and rsColumns.bof THEN
Msgbox "The table has no columns or does not exist."
rsColumns.close
SET rsColumns = NOTHING
Wscript.quit
ELSE
do until rsColumns.eof
strColumns = strColumns & rsColumns("COLUMN_NAME") & ","
rsColumns.movenext
loop
'Take off last comma
strColumns = left(strColumns,len(strColumns)-1)
rsColumns.close
SET rsColumns = NOTHING
END IF
'Ninja
strSQL = "SELECT " & strColumns & " from " & tableName & " ORDER BY " & strColumns & " asc"
SET rsserver1 = CreateObject("adodb.recordset")
rsserver1.Open strSQL, Connstring1, 1,1
IF rsserver1.eof and rsserver1.bof THEN
Msgbox "No Rows to Compare in Table 1"
rsserver1.close
SET rsserver1 = NOTHING
wscript.quit
END IF
SET rsserver2 = CreateObject("adodb.recordset")
rsserver2.Open strSQL, Connstring2, 1,1
IF rsserver2.eof and rsserver2.bof THEN
Msgbox "No Rows to Compare in Table 2"
rsserver1.close
SET rsserver1 = NOTHING
rsserver2.close
SET rsserver2 = NOTHING
wscript.quit
END IF
IF rsserver1.GetString() = rsserver2.GetString() THEN
Result = TRUE
ELSE
Result = FALSE
END IF
rsserver1.close
SET rsserver1 = NOTHING
rsserver2.close
SET rsserver2 = NOTHING
MsgBox Result
END SUBAlthough VBScript is a favorite of mine, I'm a bit surprised that it's still a requirement on new project. Anyway, thanks for the distraction, friend. It was nice to get away from VM troubleshooting for a bit.
VMWare ESXi 3.5: Creating an ISO Image from a CD/DVD-ROM
I always use ISOs in the datastore as CD/DVD devices in my virtual machines. Tonight, I had to make images of my Windows 2008 DVDs but because my tiny laptop doesn't have a DVD/CD-ROM build-in, I had to rely on my ESXi servers to create the ISOs. Creating an ISO in ESXi is as almost easy as creating one in Linux -- the only thing that presents a small challenge is VMware's use of an unconventional device path for the DVD/CD-ROM. Ultimately, I used VIC (VM->Properties->CD/DVD->Host Device) to find the name of my device, which turned out to be "/vmfs/devices/genide/vmhba0\:0\:0"
After opening up the SSH port on ESXI by using this tutorial, I created an ISOs folder in the datastore, placed the DVD in the drive and, finally, created the ISO by issuing the following command:
dd if=/vmfs/devices/genide/vmhba0\:0\:0 of=/vmfs/volumes/datastore3/isos/win2k8EEx64.isoVMware Server 2 for Linux: HOW-TO Disable VMware Virtual Infrastructure Web Access
Many people are not aware that VMware Server's resource intensive Web Access/Web MUI is not required to administer VMware Server 2. As I detailed in a previous post, the same Windows-only thick client used to administer ESX Server, Virtual Infrastructure Client (VIC) 2.5, can also be used for administration.
While the "webAccess" MUI can be disabled, disabling the rather lightweight HTTP/HTTPS services entirely is not recommended, as they are required for VIC to work. I still can't believe how much RAM webAccess requires -- more than quadruple that of the virtualization engine itself!
According to pmap, webAccess (which can be found at /usr/lib/vmware/webAccess/java/jre1.5.0_15/bin/webAccess) uses ~485MB of RAM while the virtualization engine, hostd uses just 120MB. So if you'd like to disable that monstrosity, you will have to modify one file. If you don't want to do so without shutting down the virtualization engine itself, you will have to modify the same file twice. Here's how to do it without shutting down the engine.
Modify /etc/init.d/vmware, comment out line 1202:
#vmware_stop_hostdNext, shut down webAccess.
service vmware-mgmt stopThen, open up /etc/init.d/vmware again and uncomment line 1202 then comment out line 1901.
#vmware_start_webAccesshostd is already running so you don't need to stop/restart anything at this point. Now you're done -- later hosebeast of a poorly designed MUI. 
Btw, If you want to turn off HTTP/HTTPS access completely (strangely, I didn't see any reduction in memory usage when I did this), comment out the _entire_ <proxysvc> section in the file /etc/vmware/hostd/proxy.xml and restart hostd.
service vmware-mgmt stop && service vmware-mgmt startEdit: Joe @ 4sysops.net points out a downside:
The only down side to doing that is if you use the VI Client you can only create and manage virtual machines at version 4, not version 7 like the web interface uses. So, if you create a VM in WI, you can’t manage it with VI Client. You would want to pick one or the other to use primarily. I prefer the web interface, unless I were to add a VMware Server 2.0 machine to Virtual Center. I am hoping they update virtual center at some point to support version 7 virtual machines.
So you may want to consider that before disabling webAccess. Ultimately, I ended up wiping VMWare Server 2 and using ESXi 3.5 instead.
New Network for the New Year
Now that my DL380 is stacked with a total of 10 gigs of ram, it's time to revamp my network. A majority of my servers are still in Silicon Valley (San Jose, specifically) at a fantastic colo company, Silicon Valley Web Hosting so in order to have as little downtime as possible, I'm going to temporarily push that one server to its limit and run a slimmed down virtualized network on it. Since I like my servers to be within driving distance, I'm choosing to migrate the network to Austin, TX (likely at onramp.) Granted Austin is a good 6 hours away, but colocation is much more affordable there than it is in Louisiana and any reason is a good enough reason to visit Austin.
All these new servers will require new names which is cool because the current names of my servers are a random hodgepodge of Cajun references like ROUX (the base for gumbo), BOUDIN (a delicious Cajun sausage) and BOUDREAUX (a famous Cajun last name). I'd like a little more order so I decided to get my theme together beforehand. That way, I don't have to invest time into thinking about names when I create new servers. As I was sitting in the parking lot at Acadian Food Mart in Duson, LA, I decided on the following theme: the names of small cities, towns and villages in Acadiana that are short and double as common first names or nicknames (omg I'm a nerd.) As I was documenting the list of potentials in an email to my business partner and best friend, Brandon, I figured why the hell not document them in Google Maps And so I did...
View Larger Map
So the list currently consists of Cade, Leroy, Mack, Katy, Effie, Ellis, Esther, Perry, Cecilia, Elba, Lucy, Henry, Ariel, Vick, Chloe, Lucas, Oliver, Scott, Milton, Jacoby, Brooks, Oscar, Louisa and Coon.
"Coon?" you may be asking yourself. Yes, I know two Coons, one Coonie and one T-Coon. All of whom got their nickname from the controversial epithet "coonass." So far, a few of the roles are: VMware Server, FSMO DC, Backup DC, SQL Server 2008, Backup SQL Server (Mirroring), IIS Web Server, Windows Dev Server, LAMP Server, and uhhh I'll come up with a few more once I get my other servers from SVWH. Did you know that Windows Server 2008 Enterprise edition allows 4 VM installs per physical license? Fortunately, I was able to obtain one of those bad boys at a conference last year and now I have pretty much more than enough Windows 2008 instances. Totally can't wait to try Core w/Powershell.
HP DL380 G4 and VMware: Will NOT Run 64-bit Virtualized Guests.
I mean, I'm not upset about it. I'm just sayin. I read all the Internet debates about how it might or it might not and I can confirm that I tried it and it didn't work. The server runs the host OS, SuSE Linux 64-bit, beautifully but it totally craps out when a 64-bit guest OS install is attempted. EM64T doesn't do VT so.. if that's what you're looking for, start with the HP DL380 G5.
This server still has its benefits however. Including 6 drive slots (2x72,2x147,2x36 SCSI YAY), capacity for up to 12 GB of RAM (I installed 10) and it's decently affordable. Another thing that I love about it is that I can change the POST text. How fun is that? So I made it say something that makes me laugh everytime. See if you can spot it:
Mmm 10240 MB Initialized... *drool*
About
-
We are IT consultants
who grew up living and loving life
in Cajun Country.
Authors
Categories
What's Your Job Title?
Loading ...
information
wants to be free
