Generating DDL in SQL Server

There are a lot of options that allow the end user to programatically generate DDL in SQL Server (2000 and 2005).  But what could be a reason to programatically generate the DDLs?  Well, one of the reasons is that you can schedule database builds and do version control of your schema that way.  Another one could be to write up a script that helps in taking a copy of the database schema from one instance and clone the schema on the another instance.

In SQL Server 2000, one can use SQL-DMO API to generate the DDLs - pretty simple to do also…just requires a simple VB code and you can then chose to wrap it with a fancy UI.  One can also use the scptxfr.exe utility that ships with SQL Server 2000 and use that to automate the generation of the DDLs.  There are a lot of options that this utility provides that can be used.

In SQL Server 2005, there is a new function called object_definition() that can be used to generate the object definition (such features have existed in Oracle and DB2 for quite some time).  This function can be used to generate the definition of all the objects in the schema besides the tables and the referential integrity fixes.  A simple script that gets these object id’s from the sys.objects table can be written which then loops through the list and generates the definitions of those objects and dumps them into an output file.  One can also use the SMO API to write one’s own code to do this work –> this replaces the SQL-DMO API.

In addition to these, there are separate tools in the market that allow the generation of the DDLs (in case you want to just generate them and not automate the generation using scripting): Enterprise Manager/Query Analyzer (SQL 2000), Management Studio (SQL 2005), DB Ghost (from Innovartis), QALite (from Rac4SQL) are some of the utilities that you can use.

DNS Poisoning

What is DNS Poisoning? 

DNS poisoning, also known as DNS cache poisoning, is when a DNS server receives false update information the server believes to be correct. The server or cache is altered so that hostnames resolve to an incorrect IP address.

The end result of this poisoning is that requests by a browser for a legitimate website results are redirected to one or many malicious websites. These fake websites often ‘spoof’ the original site in look and feel, which causes the user to believe they are on the legitimate webpage. Unfortunately, the user has actually been redirected to a site who’s only purpose is to cause problems, either by uploading spyware to the user’s machine, or collecting personal information about the user by collecting data that the user enters on the fake website. For example, if a user performs online banking, and is redirected to a malicious site that actually looks like the login to an actual bank, the user will naturally try to log in. These keystrokes are recorded on the other end, and the hackers running the malicious website instantly have someone’s username and password to secure financial information.

 

Preventing DNS Poisoning 

Windows 2000 SP3 and higher, as well as Windows 2003 have measures built in to prevent poisoning.

On Windows servers running DNS you can verify that DNS poisoning protection is enabled from the DNS console. After opening the console select a server from the list. Right-click on it, select Properties, Advanced tab, and make sure that the “Secure Cache Against Pollution” check box is selected. This DNS cache protection is enabled by default. With this feature enabled, the DNS server validates that the DNS responses it receives were from valid sources.

Another way to verify your Windows server is protected against poisoning is to look at the registry in the following location:

 

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\DNS\Parameters.

 

The key name is ‘SecureResponses’. On the more recent versions of Windows mentioned above, this key should not even exist. It can either be deleted, or the value of the key can be set to 1. Windows servers prior to 2000 SP3 need to have this key, and the value should be set to 1 as well.

 

Note: Please remember to always back up your registry before making any changes!

 

Another preventive measure includes DNSSEC, which is a secure version of DNS that uses digital signatures signed with a trusted certificate to verify the authenticity of DNS data. DNSSEC is rarely used, so the majority of DNS records are not secured against spoofing.

 

Using the secure version of HTTP, HTTPS, can also be leveraged to mitigate the risk of web browsers reaching spoofed websites. HTTPS allows users to check whether or not the web server’s certificate is valid and belongs to the website’s true owner.

 

Finally, and most importantly, the best method to ensure that your servers and workstations are protected from malicious attacks, such as DNS poisoning, is to keep them up to date with the latest patches and hotfixes.