ISDATE() function returns 0 for valid DATETIMEOFFSET values?

Quite confusing but true. I was working on trying to validate a specific value if it is indeed a valid DATETIMEOFFSET data type using the ISDATE() function until I saw this forum post

"Adam,

The problem is that ISDATE returns 0 for valid DATETIMEOFFSET values."

I couldn't believe it since Books Online until I tested it out myself

IF ISDATE('2009-09-08 10:19:41.177 -05:00') = 1
PRINT 'VALID'
ELSE
PRINT 'INVALID'

This returns INVALID when, in fact, it is a valid datetime value with time zone awareness. I even tried the values provided by Books Online and still gives me invalid results. I have yet to wait for a response from the SQL Server product group regarding this but at least it gives us an opportunity to dig deper

WARNING:the CID values for both test machines are the same

There are a thousand or more issues that can come up with virtualization if not done properly. One of which is having the same machine SID in the domain in case you decide to join them to one. This happens a lot of times especially when a virtual machine is created from a template or cloned from another virtual machine without using the appropriate tools like Sysprep from Microsoft

One unique incident occurred to me while trying to troubleshoot a MS DTC communication issue between SQL Server instances. A client of ours requested for assistance to fix a distributed query that uses MS DTC. Apparently, communication between the two SQL Server instances is not happening. My usual round of troubleshooting started with a series of network connectivity tests, ranging from PING to TELNET to NETSTAT to whatever is necessary to make sure that communications between the servers are working fine. That led me to look for ways to check for connectivity specifically with MSDTC. One tool from Microsoft is DTCPing, a utility to help troubleshoot MS DTC firewall issues. While I know for a fact that firewall is not an issue in this particular case, I've decided to give it a shot. Running the DTCPing utility on both servers gave me this error message in the log

WARNING:the CID values for both test machines are the same

A quick Google search led me to this blog post and made me think that the servers might have been cloned. Sure enough, when I asked the customers about the history of the servers, they were indeed cloned VMWare images. They didn't use Sysprep to prepare the images after the cloning process, hence, the reason for having the same CID values. There's nothing wrong with VMWare here. It's just the process that's pretty screwed up. What are the chances of two machines having the same GUID values which are supposed to be globally unique across the enterprise? Very slim unless they are inappropriately cloned.

I followed the steps outlined in the blog post to fix the CID values

• Use Add/Remove Windows Components to remove Network DTC.
• Run MSDTC -uninstall in the command-line
• Delete the MSDTC keys in in the registry

HKLM/Software/Microsoft/Software/MSDTC
HKLM/System/CurrentControlSet/Services/MSDTC
HKEY_CLASSES_ROOT\CID

• Reboot the server
• Run MSDTC -install in the command
• Use Add/Remove Windows Components to add the Network DTC back.
• Restart the Distributed Transaction Coordinator service
  

Following these steps helped solve the MSDTC issue but sure enough, another issue surfaced. Since SQL Server uses MSDTC in a few of its processes like executing distributed queries, the installation got screwed up big time. When we used the server to test a disaster recovery process for the entire SQL Server instance, restoring the master database became a real pain. I spent hours trying to restore the master database but to no avail. The resolution was to simply uninstall and re-install SQL Server. Only then was I able to restore the master database successfully. Lesson learned: if the foundation is screwed up, anything built on top of it will surely be the same. That applies to just about anything, whether you're building a server or developing a character.

Extending your Active Directory Schema

While most organizations use Active Directory as their directory service, very few maximize the use of it. Several applications out there like Microsoft Exchange extend the default schema to track Exchange-related information. You can, however, extend the schema yourself by opening the Active Directory Schema snap-in from the Microsoft Management Console. Unfortunately, it is not available by default. You need to register the schmmgmt.dll using the RegSvr32.exe utility (and I thought I would never have to use this utility again). Open a command prompt and run the command below

regsvr32.exe schmmgmt.dll

After that, you can now open up the Microsoft Management Console and add the Active Directory Schema snap-in. You can now add new attributes to the objects as you wish, although, updating the existing ones is definitely not recommended

Creating Active Directory Users with Windows PowerShell

While it may seem easy to create Active Directory users using the management console, I still prefer doing it using scripts so as to make sure that they are done in a uniform, standard fashion (not to mention as fast as one can possibly do especially if you will be doing it for many users). I've referenced the scripts provided at the CodePlex site for ADSI and Active Directory for Windows PowerShell (full credit goes to them) to create users in Active Directory for Windows Server 2008. This also works for Windows Server 2003. While I may be a big fan of automation, it is important to highlight that processes are what makes automation really work. The reason I am saying this is that the CSV file can come from different sources, say, an intranet site where you ask employees to log in and key in their details. Having a process in place to make sure that users who would be entering their details in a standard way would eliminate the need to cleanse the data (I'm still thinking as a DBA here) in the long run. Plus, having a standard in place as an organization is starting out will make it flexible enough to scale as growth happens.


# define constants
$domainstr = ",dc=domainName,dc=local"
$domainnb = "domainName" # domain netbios name
$domain = "domainName.local"

$ADs_UF_NORMAL_ACCOUNT = 512 # Disables account and sets password required.

# Remember to enable the account before logging in


# Prompt user to enter the default passsword for the users
$defaultPassword = Read-Host "Please enter default Password:" -asSecureString

# Read the list of users from the CSV file
# Include other user properties in the CSV file as necessary

Import-csv users.txt | foreach
{
# Create user name based on FirstName and LastName column in the CSV file
$strUser = $_.firstName + " " + $_.lastName


#Form the LDAP string based on the OU column from the CSV file
$strLDAP = "LDAP://OU=" + $_.OU + ",OU=domainName Domain Users" + $domainstr

$target = [ADSI] $strLDAP
$newUser = $target.create("User", "cn=" + $strUser)
$newUser.SetInfo()

#Define a naming convention for the login based on your corporate policy
#This one uses the first letter of the firstname and the lastname
$userID = $_.firstName[0]+$_.lastName

#Define the other user attributes based on the columns defined in the CSV file
$newUser.sAMAccountName = $userID.ToString()
$newUser.givenName = $_.firstName
$newUser.sn = $_.lastName
$newUser.displayName = $_.firstName + " " + $_.lastName
$newUser.userPrincipalName = $_.firstName[0]+$_.lastName + "@" + $domain
$newUser.mail = $_.Email
$newUser.physicalDeliveryOfficeName = $_.Location
$newUser.title = $_.Designation
$newUser.description = $_.Designation
$newUser.SetInfo()

$newUser.SetPassword($defaultPassword.ToString())

#Normal user that requires password & is disabled
$newUser.userAccountControl = $ADs_UF_NORMAL_ACCOUNT

Write-Host "Created Account for: " $newUser.Displayname

}