How To Use Transparent Data Encryption To Increase Your Security

In addition to a general database policy and practices of securing databases such as : Security architecture, Asset encryption ,Firewalls & Regular Audits , there is an option to apply Transparent Data Encryption (TDE) to a SQL Server database - aka "data at rest encryption". TDE is about securing data in the situation of backup device\drive theft or adhoc backup file protection. Setting up TDE in SQL Server is in summary Create a master key. Create or obtain a certificate protected by the master key. Create a database encryption key and protect it by using the certificate. Set the database... Read more →

Quick Fix: How to display hours minutes seconds between two dates

Question: I need to present the difference between two dates - returned by GETDATE() , which are using the datetime data type, as hours , minutes, seconds i.e hh:mm:ss , not as columns - but as one string . For example , if its 1 hr, 10 min, 15 seconds - than it should be : 01:10:15 . How can this be done? Answer: There's a few different ways you manage this result . For example - you could use the DATEDIFF method - comparing two different dates and returning the datepart defined between the two dates. In your case... Read more →

Use Get-ADPrincipalGroupMembership with other Active Directory domains

Question: I use the Powershell cmdlet Get-ADPrincipalGroupMembership to get AD group membership of IDs within the current domain, but how can I use Get-ADPrincipalGroupMembership to check the membership of an ID in another domain? Answer: Yes , The Get-ADPrincipalGroupMembership cmdlet will return the group membership of the specified user , in the current domain of the logon executing this cmdlet . To access the group membership of a specific ID , you'll need to use the -server switch . Example Get-ADPrincipalGroupMembership 'ID1' -Server 'anotherdoamin' | select name If you want to read on how to access the group membership of... Read more →

On-Demand Instant File Initialization status with sys.dm_server_services

Configuring a SQL Server with Instant file initialization is a fairly standard part of SQL Server Installation Checklists. There are substantial benefits to the duration taken during a SQL Server database restore. If you want to read more on how to set up instant file initialization and the technical benefits read SQL Server faster restores with instant file initialisation When I first started using instant file initialization I used to manually check using the gpedit.msc console - and checking the Windows Settings | Security settings | Local Policies | User Rights Assignments. Eventually I wrote a script to check as... Read more →

Error Message:The UPDATE statement conflicted with the REFERENCE constraint

During an UPDATE statement on a particular row on a SQL Server table , this error message appeared: Error Message:The UPDATE statement conflicted with the REFERENCE constraint "FK_My_Constraint". The conflict occured in database "MyDB",table "dbo.MyTable". The statement has been terminated The initial input value was incorrect , so I needed to update the value - but there was a foreign key constraint in place to retain integrity between the tables. The way to fix this issue is to temporarily disable the FOREIGN KEY constraint - change the values and the set the FOREIGN KEY constraint again. SQL Server has a... Read more →

How to master Mount-Diskimage in Powershell

Question: I want to set up a script to Mount a Disk in an automated way utilising Powershell ? The image exists as an ISO on a network path and requires to be made available as a drive letter & path. It doesn't have to be a dedicated drive letter - just the next letter after the highest. So for example , if I already have E:, F:,G: than I want it to be set as I: . I have some code in the script which can identify the drive letter assigned Answer: The Powershell code below will create the... Read more →

Error Message: The RPC server is unavailable during SQL Setup

Question: I have received a server from the server build team and I've started a Server set up. During the set up process , I'm getting an error message Error Message :The RPC server is unavailable How cam I fix this issue? Answer: I have seen this problem in a few situations. Typically it arises when I'm using a Domain account as a service set up account. For example - I may have a service account as a member of the MYDOMAIN domain. If the server cannot connect to a valid DNS server , where the sql server service account... Read more →