About Me · Send mail to the author(s) E-mail · Twitter

At GROSSWEBER we practice what we preach. We offer trainings for modern software technologies like Behavior Driven Development, Clean Code and Git. Our staff is fluent in a variety of languages, including English.

Feed Icon


Open Source Projects


Blogs of friends

Now playing [?]

Error retrieving information from external service.
Page 1 of 2 in the SQLServer category Next Page

Migrating SharePoint Content Databases To A New Farm While Keeping Security Settings

Posted in .NET | SharePoint | SQL Server at Saturday, 25 April 2009 16:40 W. Europe Daylight Time

The server this blog is hosted on was upgraded recently, i.e. is now run on a new dedicated server. Actually, next to this blog there’s a lot more going on. We, a bunch of geeks, are self-hosting mail, web sites, blogs and some collaboration tools like SharePoint (Windows SharePoint Services, in our case). Yeah, SharePoint, a true beast in and of itself. I can’t tell you how much I do not miss developing software for it and setting up customer sites.

I wanted to make the move to the new server as smooth as possible for our SharePoint users. Because we do not use Active Directory to authenticate our users, we obviously had to migrate the SharePoint user accounts manually. That is, re-create each user on the new server giving them a random password and communicate the change.

Moving a SharePoint site is surprisingly pretty well documented on TechNet, but won’t tell you about one important aspect: When you move the site to a new farm and the site does not use Active Directory, you will have to set up security anew.

Why? Because SharePoint matches user accounts by their SID, a value that is unique for each user account, even across machines: OLDMACHINE\foo’s SID is different from NEWMACHINE\foo’s SID. Burdening the four site collection administrator with this task is simply a no-go.

During my research how to work around that I found the Dustin Miller’s excellent post “Fix those SIDs”. It describes the process of massaging a SharePoint site collection database to replace old SIDs with the account SIDs of the current machine. I’ve extended it a bit, because I also decided to rename the SharePoint Search account while moving to the new server (note the extra REPLACE in line 11).

DECLARE @login nvarchar(255), @SystemId varbinary(512)

SELECT tp_Login, tp_SystemID FROM UserInfo WHERE tp_Deleted = 0

OPEN curUsers
FETCH NEXT FROM curUsers INTO @login, @systemid

    DECLARE @newLoginName AS nvarchar(255) = REPLACE(REPLACE(@login, 'OLDMACHINE', 'NEWMACHINE'), 'spsearch', 'sharepoint-search')
    DECLARE @newSID varbinary(85) = SUSER_SID(@newLoginName)
        PRINT 'Resetting user ' + @login + ' to new login ' + @newLoginName  + ' with SID '
        PRINT SUSER_SID(@newLoginName)

        UPDATE UserInfo
        SET    tp_SystemID = SUSER_SID(@newLoginName),
               tp_Login = @newLoginName
        WHERE CURRENT OF curUsers
    FETCH NEXT FROM curUsers INTO @login, @systemid

CLOSE curUsers

After the script ran, take a look at the UserInfo table and SELECT rows that still contain OLDMACHINE in the tp_Login column. This helps you get a quick overview of what accounts have been missed during account re-creation.

As an extra step, I found it appropriate to update the site’s user entry as well (the account name that shows up in the site’s user list when no full name is given) to reflect the new machine name.

UPDATE    [AllUserData]
SET       [nvarchar1] = REPLACE(REPLACE([nvarchar1], 'OLDMACHINE', 'NEWMACHINE'), 'spsearch', 'sharepoint-search'),
          [nvarchar2] = REPLACE(REPLACE([nvarchar2], 'OLDMACHINE', 'NEWMACHINE'), 'spsearch', 'sharepoint-search'),
          [nvarchar3] = REPLACE(REPLACE([nvarchar3], 'OLDMACHINE', 'NEWMACHINE'), 'spsearch', 'sharepoint-search')

General Considerations For Securing Windows Servers On The Internet (And Anywhere Else)

Posted in IIS | Networking | PowerShell | Security | SQL Server | Windows at Friday, 08 February 2008 17:04 W. Europe Standard Time

By now there are a couple of Windows Servers that I actively manage, or, in the case of projects, I touched while moving the project forward. Most of these servers have an Internet connection. Since I've been asked how to make servers more secure and manageable, here are a couple of management rules I applied. Consider it a checklist.

  • Use a firewall and configure it accordingly.
  • Enable automatic Windows Updates and upgrade to Microsoft Update to receive updates of other Microsoft products like SQL Server.

Okay, the two above should have been obvious.

  • Keep the machine clean.
    Don't install any unnecessary software and don't leave any temporary files on the server. I've seen certificate requests lingering on drive C: and "test" folder remnants. A clean system might reveal hacker activity early in case they leave unfamiliar files behind.
  • Leverage the principle of least privilege.
    All users and service accounts should only have the minimum rights they need. Configure the file system such that system services can only access the files and folders they are in charge of. Typical example: Use a dedicated service account for SQL Server. (Setting this up on SQL 2005 is even more simple.)
  • Rename the Administrator account.
    Rename the Administrator account and make it match your preferred user naming scheme (i. e. agross). You might apply this to your whole organization if you use Active Directory. This is another hurdle to guess the Administrator account from a list of user accounts and works good with account lockout enabled (see below).
  • Create a new "Administrator" account
    and give it a very strong throw-away password. I typically use two or three concatenated GUIDs that I immediately forget. Disallow this user to change his password, remove all group memberships and disable the account.
  • Audit the hell out of the machine.
    Windows uses the Security event log to record security-related events. Configure auditing using secpol.msc and enable success and failure logging at least for
    • Account logon events,
    • Logon events and
    • Policy change.
    The last option is for tracking changes to the policy you just set.
  • Enable complexity criteria for passwords and account lockout.
    To be found in the same MMC snap-in as above. For account lockout I often go with the default values of 5 attempts and 30 minutes of threshold and duration.
  • Deactivate file sharing/Microsoft Networking on the WAN connection.
    Because it's most likely unneeded.
  • Secure RDP sessions using a certificate.
    Torsten has a nice write-up on how to leverage SSL to secure the RDP handshaking/authentication phase to overcome man-in-the-middle and ARP spoofing attacks. His article is available in German only so here's two-sentence recap: On the server's RDP security tab, enable SSL and choose the same certificate you use for HTTPS encryption. On the client side, enable server authentication.
  • Extra: Allow RDP sessions only from white-listed workstations.
    For a server that was hacked a while ago using an ARP spoofing attack (see bullet above) I wrote a Powershell script forces RDP session to originate from a list of known workstations. Each RDP user can have multiple allowed workstations. If a logon attempt occurs from another machine that RDP session is killed immediately.
    # Alert.ps1
    # Logon script for users with known RDP client names.
    # Array of users with known workstations.
    $userWorkstations = @{
    	"user2" = @("VALIDCOMPUTERNAME3")
    # Logoff executable.
    $logoffCommand = $Env:SystemRoot + "\system32\logoff.exe"
    # Trim the user name.
    $currentUser = $Env:UserName.Trim()
    # Cancel if a user that's not contained in $userWorkstations logs on.
    if ($userWorkstations.Keys -inotcontains $currentUser)
    # Send alert e-mail and log off if the user logs on from an unknown workstation.
    if ($userWorkstations[$currentUser] -inotcontains $Env:ClientName.Trim())
    	$subject = $("Unknown RDP client '{0}' for user '{1}'" -f $Env:ClientName.Trim(), $currentUser)
    	$message = New-Object System.Net.Mail.MailMessage
    	$message.From = ""
    	$message.IsBodyHtml = $false
    	$message.Priority = [System.Net.Mail.MailPriority]::High
    	$message.Subject = $subject
    	$message.Body = $subject
    	$smtp = New-Object System.Net.Mail.SmtpClient
    	$smtp.Host = "localhost"
    	# Force logoff.
    Set the script as a logon script for your users using the Alert.cmd helper script below.
    rem Alert.cmd - runs the Alert.ps1 Powershell script.
    @powershell.exe -noprofile -command .\Alert.ps1
  • Enable SQL Server integrated authentication.
    I don't see a need for SQL Server Authentication in most scenarios, especially if you're running/hosting .NET applications. However, in some projects I've worked on there seems to be a tendency towards SQL Server Authentication for no special reason.
  • Configure IIS log detail and directories.
    I tend to enable full IIS logs, that is, all information regarding a request should be logged. Also, I like my logs residing in "speaking" folders, so instead of %windir%\system32\LogFiles\W3SVC<Some number> they should be placed in %windir%\system32\LogFiles\IIS <Site name>\W3SVC<Some number>. This makes it easy to find the logs you're interested in.
  • Use SSH to connect remotely.
    There's a little free SSH server out there that should fit most user's needs. Besides a secure shell environment freeSSHd offers SFTP and port tunneling capabilities to tunnel insecure protocols. Authentication works natively against Windows accounts.
  • Deploy a server monitoring tool.
    I like to use the free MRTG tool, of course any other tool allowing you quickly view any uncommon activity to will do.
  • Use a dedicated management network interface, if possible.
    You should configure strict firewall rules for that interface. Allow access only from a known management subnet.

What rules do you apply to make your servers more secure and manageable?

Now Playing [?]: MorcheebaDive DeepEnjoy the ride (feat. Judy Tzuke)

The Case Of The Giant SQL Server Log Files

Posted in SQL Server at Wednesday, 30 January 2008 19:10 W. Europe Standard Time

The day before yesterday I had to look into a SQL Server 2005 (Service Pack 2) instance where the daily backups of a certain database amounted to several Gigabytes over the last 14 days. The database in question is only about 10 MB in size and the recovery model is set to "Simple" so that should not be the case. The problem was a giant log file, around 6 GB in size by the time I started the remote session.

First I tried shrinking the database and the logs via the Context Menu, Tasks, Shrink Database options, but shrinking the log file failed miserably: it would not decrease below a magic threshold of 5,5 GB. Shrinking even should not free that much disk space because SQL Server shrinks the logs automatically after a transaction is committed when the recovery model is set to "Simple".

After a while of trial and error I realized that there must be some open transaction that has not been committed or otherwise been marked as done. But how to finally get rid of it?

First I tried to change the recover model to "Full" and "Bulk-logged", backing up the database and the logs separately.

BACKUP DATABASE database TO DISK='C:\Temp\file_data.bak' WITH NO_LOG
BACKUP LOG database TO DISK='C:\Temp\file_log.bak'

Processed 1000 pages for database 'database', file 'database_Data' on file 2.
BACKUP DATABASE...FILE= successfully processed 1000 pages in 0.434 seconds (18.875 MB/sec).

Processed 807822 pages for database 'database', file 'database_Log' on file 3.
The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed.
BACKUP LOG successfully processed 807822 pages in 15.077 seconds (48.119 MB/sec).

As you can see from the messages above the shrink operation could not shrink the whole log file. The hint to use sp_repldone is a bit misguiding because the database isn't replicated. In fact, the replication components aren't even installed on the server. As expected, executing sp_repldone fails:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1
Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication.

I digged the SQL Server Books Online for more information. First of all there's a command to find out about open transactions: DBCC OPENTRAN. As you can see there's at least one transaction that hasn't been replicated.


Replicated Transaction Information:
   Oldest distributed LSN     : (0:0:0)
   Oldest non-distributed LSN : (3822:331:1)
   DBCC execution completed. If DBCC printed error messages, contact your system administrator.

At least, it was the hint that finally solved the issue: There's a transaction that SQL Server thinks should be distributed to a replicated server, although there is none configured.

Fortunately, there's a stored procedure that can be used to disable replication, even if it wasn't enabled in the first place. I suspect sp_removedbreplication cleans up some bogus settings, at least it was able to remove the transaction that kept the log file steady at 5,5 GB.

Two hours later: Problem solved!

Now Playing [?]: Ocker – Public Transport – Last night the secret service saved my life

How To Obtain The Size Of All Tables In A SQL Server Database

Posted in SQL Server at Monday, 19 November 2007 14:08 W. Europe Standard Time

In a current project we were wondering why an application database grew quickly during the last couple of weeks of development and testing. We wanted to know the space used of each table without using Management Studio's table properties.

Table Properties

Actually SQL Server gives you everything you need with its Stored Procedure sp_spaceused. Unfortunately this SP does not support iterating over all tables in a database, so we needed to leverage another (undocumented) Stored Procedure sp_msForEachTable.



-- DB size. EXEC sp_spaceused

-- Table row counts and sizes. CREATE TABLE #t
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''


-- # of rows. SELECT SUM(CAST([rows] AS int)) AS [rows]


The screen shot below shows the results of a smaller testing database.

Table Sizes

Now Playing [?]: TerranovaDigital TendernessCommon places

Running SQL Server 2000 with Limited Rights

Posted in SQL Server at Monday, 23 January 2006 18:25 W. Europe Standard Time

I often see SQL Server 2000 installations where the server is left running under the SYSTEM account. This is not a good idea because if SQL Server gets compromised, the attacker will have full control over the machine. Even if the server is running under an account with Administrator or Power User rights similar risks arise. If the account in question is a domain account, the attacker will have access to other systems of your organization as well.

I suspect the root cause for these often-seen SYSTEM-level instances lies in the fact that the SQL Server 2000 setup program suggests running the SQL Server services under the SYSTEM account.

There are actually ways to run SQL Server 2000 under a Limited User Account without having to sacrifice functionality. The administrative tools of Windows and SQL Server will help you to accomplish better security for your server. The settings made below may be applied earlier during the setup program. However, I'll describe how to change them after the installation has finished.

  1. Create a new local or domain account to be used just for SQL Server.
    Create a New UserOpen MMC or AD User Management to create a new user. Use the default naming convention deployed in your organization. This will it make more difficult to hackers that are trying to identify the account (I'll use smeier/Susanne Meier as an example). Set the password to never expire and revoke the right to change the password. Use a strong password containing numbers and special characters.
  2. Remove default group membership.
    Remove New User from Users GroupNewly created accounts are members of the User group by default. Remove this membership, it won't be needed. This also prevents interactive logons using this account.
  3. Tell SQL Server to use the newly created account.
    Set new SQL Server AccountOpen the Enterprise Manager, select the SQL Server instance you want to secure. Right-click, select Properties and go to the Security tab. On the bottom you'll see the account settings. Enter the name of the newly created account and the password.
  4. Let Enterprise Manager modify the rights for the service account.
    After clicking the OK button Enterprise Manager (SQLEM) will grant the rights required to run the SQL Server service to your SQL Server account. The SQL services will be restarted during this process.
  5. Set the NTFS rights on your database files.
    Modify NTFS RightsThe SQL Server account needs change rights on the *.mdf and *.ldf files of your databases. SQLEM will modify the rights of the <SQL Server Path>\MSSQL\Data directory automatically, which is the default path for new databases. For all databases outside this folder you will have to modify the rights manually using the File Properties Dialog.
  6. Restart SQL services.
    This step is only needed if you had to set NTFS rights manually in step 5.
  7. Verify that all databases are working.
    Use SQLEM and/or the Windows Event Log to check that all databases could be loaded successfully. If errors occur, it's likely that NTFS rights are set incorrectly, i.e. the datebase files are inaccessible.

That's it. Your SQL Server instance is now running with limited rights. In case an attacker is able to gain access to the server he will be constrained in his abominable deeds. A side note: This makes sense on development machines too. Deploying security not only on production servers is key to best practice development.

Happy SQL'ing!

Page 1 of 2 in the SQLServer category Next Page