PowerDNS on SuSE Linux & SQL Server 2005 – Part 1

Standard

I have been working on a scenario for one of our services and that’s our DNS back-end. It has to achieve the following criteria.

  • A Fast and Reliable DNS Service
  • SQL Server Database Back-end for management. (as we already run MS SQL as standard)
  • Linux Based DNS Server (better supported, free & reliable)
  • VMware Virtual Server

So after a good look around we have decided on PowerDNS, SuSE Linux Enterprise 10.1 & MS SQL Server 2005 as the DBMS.

Now this is where the fun starts. I’m no Linux expert but I can find my way around pretty well. Let me tell you, getting this to work was not easy, 3 long days of head scratching, disparate documentation and perseverance. This is probably the only HOWTO there is on doing this.

Assumptions.

  1. You already have SQL 2005 Installed and available on the network. 
  2. Your relativity competent in Windows / Linux administration. 
  3. You understand DNS concepts.

Steps to install SuSE on VMware Workstation

  1. Create your virtual machine environment and start it up. In my case I have chosen VMware Workstation 5 with ESX Server 3 compatibility. 512MB of RAM and 4GB of disk space split into 2GB files.
  2. Install SuSE Linux by mounting the ISO image on the virtual machines CDROM drive. I have chosen just to install the base server system but included the C++ compilation tools for building software.
  3. Once SuSE is installed you need to install the VMware tools. Select this from the VM menu. Once installed run the command # /usr/bin/vmware-config-tools.pl this sets up the proper screen drivers and allows things like shared folders, copy & paste between virtual machine and the host.

Hit ctrl-backspace to restart the X windows system. You should now have a base Linux System running under VMware.

First let’s get all the files we need :-

PowerDNS v2.9.22 (Authoritative Name Server)
http://download.opensuse.org/repositories/server:/dns/openSUSE_10.3/i586/pdns-2.9.22-1.1.i586.rpm

PowerDNS v2.9.x OpenDBX Backend (PDNS Database access via OpenDBX)
http://download.opensuse.org/repositories/server:/dns/openSUSE_10.3/i586/pdns-snapshot-backend-opendbx-2.9.x.r1107-3.3.i586.rpm

OpenDBX Library (DBMS API)
http://linuxnetworks.de/opendbx/download/opendbx-1.2.3.tar.gz

FreeTDS Library (allows communication from Linux to various DBMS including SQL Server
ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz

MySQL header libraries as it is a dependency for PDNS.
http://download.opensuse.org/repositories/server:/database/openSUSE_10.3/i586/libmysqlclient15-5.0.67-20.1.i586.rpm

Create a suitable directory on you Linux OS and copy the downloaded files to this directory. I have used /root/Documents/PDNS. The shared folders directory on the Linux guest is under /mnt/hgfs.

Once you have the files extract the tar’s for OpenDBX & FreeTDS.

Installing FreeTDS and Connecting to MS SQL Server

The first item we need to install is FreeTDS, basically TDS stands for Tabular Data Stream and is the protocol that MS SQL Server uses to communicate with it’s clients. You can read more about it @ http://www.freetds.org/

Open a terminal window up, change directory to where you extracted the FreeTDS software to.

Run the following commands to build and install it:-

$ ./configure
$ make
$ make install

Great, if you didn’t get any errors we can now configure the connection. Load up gedit and open /usr/local/etc/freetds.conf go the end of the file and add the following:-

# My connection to SQL Server 2005 on PC12SQL2005D_1
[MySQLServer2005]
 host = pc21.outerin.uk-net
 instance = SQL2005D_1
; port = 1433
 tds version = 8.0

Subsitute the host, instance or port for that of your SQL Server Instance. If you use instance then the port is auto-detected. Save the file and quit.

NOTE : Make sue you can resolve the remote SQL server and you can get a response. If you can’t make sure

a) etc/hosts file has an entry
b) your DNS server is correct.
c) any firewalls allow the SQL traffic through.

Lets test the connection by running the command tsql.

 

Excellent we now have a connection to SQL Server.

Set up the PowerDNS Database on MS SQL Server.

Execute this script on SQL Server

USE [master]

GO

/****** Object:  Database [PowerDNS]    Script Date: 10/17/2008 12:15:22 ******/

CREATE DATABASE [PowerDNS] ON PRIMARY
( NAME = N'PowerDNS_Data', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAPowerDNS_Data.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
    LOG ON
( NAME = N'PowerDNS_Log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAPowerDNS_Log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname = N'PowerDNS', @new_cmptlevel = 90
GO
IF ( 1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') )
    begin
        EXEC [PowerDNS].[dbo].[sp_fulltext_database] @action = 'disable'
    end
GO
ALTER DATABASE [PowerDNS] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [PowerDNS] SET ANSI_NULLS OFF
GO
ALTER DATABASE [PowerDNS] SET ANSI_PADDING OFF
GO
ALTER DATABASE [PowerDNS] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [PowerDNS] SET ARITHABORT OFF
GO
ALTER DATABASE [PowerDNS] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [PowerDNS] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [PowerDNS] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [PowerDNS] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [PowerDNS] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [PowerDNS] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [PowerDNS] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [PowerDNS] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [PowerDNS] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [PowerDNS] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [PowerDNS] SET ENABLE_BROKER
GO
ALTER DATABASE [PowerDNS] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [PowerDNS] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [PowerDNS] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [PowerDNS] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [PowerDNS] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [PowerDNS] SET READ_WRITE
GO
ALTER DATABASE [PowerDNS] SET RECOVERY FULL
GO
ALTER DATABASE [PowerDNS] SET MULTI_USER
GO
ALTER DATABASE [PowerDNS] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [PowerDNS] SET DB_CHAINING OFF
GO

USE [PowerDNS]
GO
/****** Object:  Table [dbo].[domains]    Script Date: 10/17/2008 12:21:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[domains]
    (
      [id] [int] IDENTITY(1, 1)
                 NOT NULL,
      [name] [varchar](255) NOT NULL,
      [type] [varchar](6) NOT NULL,
      [master] [varchar](40) NOT NULL
                             DEFAULT ( '' ),
      [account] [varchar](40) NOT NULL
                              DEFAULT ( '' ),
      [notified_serial] [int] NULL,
      [last_check] [int] NULL,
      [status] [char](1) NOT NULL
                         DEFAULT ( 'A' ),
      CONSTRAINT [pk_domains_id] PRIMARY KEY CLUSTERED ( [id] ASC )
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY],
      CONSTRAINT [unq_domains_name] UNIQUE NONCLUSTERED ( [name] ASC )
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
    )
ON  [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[records]    Script Date: 10/17/2008 12:21:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[records]
    (
      [id] [int] IDENTITY(1, 1)
                 NOT NULL,
      [domain_id] [int] NOT NULL,
      [name] [varchar](255) NOT NULL,
      [type] [varchar](6) NOT NULL,
      [ttl] [int] NULL,
      [prio] [int] NULL,
      [content] [varchar](255) NOT NULL,
      [change_date] [int] NULL,
      CONSTRAINT [pk_records_id] PRIMARY KEY CLUSTERED ( [id] ASC )
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
    )
ON  [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[supermasters]    Script Date: 10/17/2008 12:21:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[supermasters]
    (
      [ip] [varchar](40) NOT NULL,
      [nameserver] [varchar](255) NOT NULL,
      [account] [varchar](40) NOT NULL
                              DEFAULT ( '' )
    )
ON  [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[records]
        WITH CHECK
ADD CONSTRAINT [fk_records_domainid] FOREIGN KEY ( [domain_id] ) REFERENCES [dbo].[domains] ( [id] ) ON UPDATE CASCADE
        ON DELETE CASCADE
GO
ALTER TABLE [dbo].[records]
        CHECK CONSTRAINT [fk_records_domainid]

USE [master]
GO
CREATE LOGIN [powerdns] WITH PASSWORD= N'secret', DEFAULT_DATABASE= [PowerDNS], DEFAULT_LANGUAGE= [us_english], CHECK_EXPIRATION=
    OFF, CHECK_POLICY= OFF
GO
ALTER LOGIN [powerdns] ENABLE
GO
USE [PowerDNS]
GO
CREATE  USER [powerdns] FOR LOGIN [powerdns]
GO
GRANT DELETE ON [dbo].[domains] TO [powerdns]
GO
GRANT INSERT ON [dbo].[domains] TO [powerdns]
GO
GRANT REFERENCES ON [dbo].[domains] TO [powerdns]
GO
GRANT SELECT ON [dbo].[domains] TO [powerdns]
GO
GRANT UPDATE ON [dbo].[domains] TO [powerdns]
GO
GRANT DELETE ON [dbo].[records] TO [powerdns]
GO
GRANT INSERT ON [dbo].[records] TO [powerdns]
GO
GRANT REFERENCES ON [dbo].[records] TO [powerdns]
GO
GRANT SELECT ON [dbo].[records] TO [powerdns]
GO
GRANT UPDATE ON [dbo].[records] TO [powerdns]
GO
GRANT SELECT ON [dbo].[supermasters] TO [powerdns]
GO

Build and Install the OpenDBX Library

We need to build the OpenDBX library backend for SQL Server 2005 (for other backends please refer to http://www.linuxnetworks.de/doc/index.php/OpenDBX for more info)

Change to the OpenDBX directory where you extracted the files and issue the command

$ ./configure –with-backends=”mssql”
$ make
$ make install

Once completed we need to install PowerDNS and the PowerDNS OpenDBX backend.

Install the MySQL header libraries as it is a dependency for PDNS.

Hit Install to install the package

Install the pdns-2.9.21-17.4.i586.rpm package

Hit Install to install the package.

Install the OpenDBX backend

This package will not install. Instead open it in “File Roller” and extract the contents to /usr/lib/pdns this is where they are looked for.

Testing the PowerDNS installation

Once installed test the PowerDNS installation by issuing the command:-

$ /etc/init.d/pdns monitor

you should see an error message as below.


This means we have successfully installed the PowerDNS server and we now need to configure it. Open the file /etc/pdns/pdns.conf in gedit

Find the following section

#################################
# launch Which backends to launch and order to query them in
#
# launch=

and change to:-

#################################
# launch Which backends to launch and order to query them in
#

launch=opendbx
opendbx-backend=mssql
opendbx-host-read=MySQLServer2005
opendbx-host-write=MySQLServer2005
opendbx-database=PowerDNS
opendbx-username=powerdns
opendbx-password=secret
opendbx-sql-transactbegin=BEGIN TRANSACTION

NOTE: make sure there are no spaces next to the = sign. It won’t work 

Try the command again:-

$ /etc/init.d/pdns monitor

Success we have a Database driven DNS system!!

Testing the DNS

Enter the command

$ host http://www.example.com 127.0.0.1

The address will not be resolved as we haven’t any data in the database yet. Run the following SQL on the database (you can change the IP addresses and names to suit you own).

USE
[PowerDNS]
GO
INSERT
INTO domains (name, type) values ('example.com', 'NATIVE');
INSERT
INTO records (domain_id, name, content, type,ttl,prio) VALUES (1,'example.com','localhost postmaster@example.com 1','SOA',86400,NULL);
INSERT
INTO records (domain_id, name, content, type,ttl,prio) VALUES (1,'example.com','ns1.outerin.net','NS',86400,NULL);
INSERT
INTO records (domain_id, name, content, type,ttl,prio) VALUES (1,'example.com','ns2.outerin.net','NS',86400,NULL);
INSERT
INTO records (domain_id, name, content, type,ttl,prio) VALUES (1,'www.example.com','192.168.1.100','A',120,NULL);
INSERT
INTO records (domain_id, name, content, type,ttl,prio) VALUES (1,'mail.example.com','192.168.1.252','A',120,NULL);
INSERT
INTO records (domain_id, name, content, type,ttl,prio) VALUES (1,'localhost.example.com','127.0.0.1','A',120,NULL);
INSERT
INTO records (domain_id, name, content, type,ttl,prio) VALUES (1,'example.com','mail.example.com','MX',120,25);

Now try the command again. Your should see a returned record, success; we are getting result and our setup is working.

In the next part I’ll be increasing the security and getting the DNS to start up automatically. I’ll also be looking at the master/salve configuration and seeing how it ties together.

Thanks to Norbert @ Linux Networks for getting me over the last hurdle. Check out the following website for further information.

http://www.linuxnetworks.de
http://www.powerdns.com
http://www.freetds.org
http://software.opensuse.org
http://download.opensuse.org/repositories

Regards

Tobi