MS SQL Server "Plus" Plug-In [KB1249]

This plug-in queries a Microsoft SQL Server for one or more DNS records of any type.

The "Plus" in the title is to signal that this plug-in is more powerful (and more difficult to use correctly) than the original MS SQL Server Plug-In.
It is more powerful because it can be fetch multiple DNS records of any type, whereas the original can only fetch a single host or reverse record.
This "Plus" version also requires an "unlimited zones" license for Simple DNS Plus, whereas the original works with any license size.

SQL queries are executed asynchronously (in a separate thread) and therefore won't slow down other requests not using the plug-in.

To setup a new MS SQL Server Plus plug-in instance; in the Simple DNS Plus (version 5.1 build 111 or later) Options dialog / Plug-ins section, select the "MS SQL Server Plus" component and click the "Create new instance..." button.

In the plug-in instance dialog / Plug-In Settings tab you can specify the database connection string and SQL SELECT statement to be used to fetch records:

And in the plug-in instance dialog / Ask About tab, you can specify which domain name and record types the plug-in will be queried about:

There are no specific requirements for database table layout. You just need to be able to execute an SQL query which returns the required data. The query can be based on a table, view, stored procedure, or any other valid SQL expression.

Your SELECT statement may optionally include the parameters @QNAME (the requested domain name), @QTYPE (the requested DNS record type), and @QIP (the IP address where the DNS request came from).

Your SELECT statement must return a "RecData" column with DNS record data in zone file format, and can optionally return "RecName", "RecType", "RecTTL", and "RecSec" columns.
It is important that the string data returned in the RecData column is in the correct format as defined by the DNS RFCs.
For example the data for an MX-record might be: "10 mail.example.com." (without quotes), and for a TXT-record: "sample text" (WITH quotes).

NOTE: Serving data from directly from a SQL server with this plug-in can be very powerful, but keep in mind that querying a SQL server will never be as fast as serving DNS records directly from RAM, as Simple DNS Plus does when serving data from local zones. Therefore we recommend that you limit plug-in queries to only those specific domains and record types that you have stored in SQL.

REFERENCES:
For more information, please see the following knowledge base articles:

Comments:

	06 Jul 2009 14:25 GMT - by mike: I've managed to get simpledns to use a sql table, however I cannot get the format of an SOA record correct. It always reports RCODE 2 - Server Failure. Any ideas? Also is there a way to get DNS records from within simpledns in to SQL server? we're looking at using simpledns with SQL however we need to populate SQL first :) Thanks in advance...
	06 Jul 2009 14:35 GMT - by mike: have you got a version of simpledns that saves its to a sql database rather than flat text files? :)
	06 Jul 2009 15:34 GMT - by Jesper - JH Software: Hi Mike, The format for the SOA record data is: <email> <primary-dns> <serial> <refresh> <retry> <expire> <minimum-ttl> For example: hostmaster.example.com ns1.example.com 1 3600 1800 86400 3600 Note that @ in the e-mail address must be replaced by a dot. No, we do not currently have a version that load zones from a database instead of flat files. This is however something that we are considering for future versions.
	06 Jul 2009 19:00 GMT - by mike: Thanks so much for the quick response, the SOA works great now! I really like the simpledns application and I'm going to push to get it in use for our primary and secondary dns servers, as the store will be in SQL I'm assuming that if we install the app on 2 servers they can both use the same store? Also to save entering the domains manually in to SQL do you know of a program that would import flat text dns files in to a sql database?
	06 Jul 2009 21:25 GMT - by mike: also, sorry to bug but any ideas on what the recdata and recname would be for a CNAME record? :)
	08 Jul 2009 07:24 GMT - by Jesper - JH Software: Yes - two servers can use the same store. CNAME record data would just be a domain name (FQDN).
	08 Jul 2009 07:32 GMT - by mike: Hi, Thanks for that, I've tried setting up a CNAME for a www.qwerty.com but it doesn't appear to work? I've got an A record with a recdata of 1.1.1.1 and a RecName of qwerty.com and I've tried www as the RecData and qwerty.com as the RecName but it doesn't appear to work. i.e. if I try to ping qwerty.com this works great and returns 1.1.1.1 however if I ping www.qwerty.com it doesn't return 1.1.1.1 I think I'm missing something really simple? ta! P.S. Any ideas on anything available to import txt dns files in to a SQL table? :)
	09 Jul 2009 23:24 GMT - by Jesper - JH Software: You cannot have a CNAME record with the same name (RecName) as the zone it is in - this will always conflict with the SOA- and NS-records. But you can have a CNAME record named "www.qwerty.com" (RecName) pointing to "qwerty.com" (RecData). As for importing - I guess this depends on what format your text file is in, but some script / SQL combination should certainly be possible.
	10 Jul 2009 07:24 GMT - by mike: Hiya, Thanks for the response, I've tried a recdata of "qwerty.com" and a recname of "www.qwerty.com" but this doesn't appear to work :( I feel like I'm being a pain in the ass! but any help would be welcome :) ta.
	10 Jul 2009 09:54 GMT - by Jesper - JH Software: It must be the other way around (recname/recdata).
	29 Nov 2009 23:15 GMT - by MFord: We're trying this out now and have a couple questions: 1. How can we have the plug in respond to wildcard A hostname records (normally we'd add a "*.somedomain.com" wildcard record to a normal zone file to serve unknown hostnames.) This does not seem to work with the plugin. 2. How can we specify to perform normal recurive lookup if domain's SOA is not found in the SQL query (i.e. we don't host DNS for the queried domain but still want to look-up and serve DNS results to the caller). 3. Will secondary SimpleDNS SuperSlave servers be able to pull the entire zone if the domain is served by this plugin, or do we need to set up all secondary servers using the same plugin & database?
	30 Nov 2009 11:05 GMT - by Jesper - JH Software: Hi MFord, 1) The domain name passed to SQL (@QNAME) is the full domain name from the DNS request (for example "host.somedomain.com"). So if you want to match any name with a specific ending, you must either handle this in SQL statements (using 'substring' etc), or limit the plug-in to only handle specific domains by settings up a rule in the DNS Requests tab (here you can specify wildcard format directly). 2) This is automatic - if the SQL query does not return anything (0 rows), Simple DNS Plus will go on processing the DNS request as it would without the plug-in. 3) Plug-ins are not automatically synchronized between super master/slave servers. So you need to configure this on both servers.
	01 Dec 2009 05:00 GMT - by Mark: 1. do you provide the procedure to create database? 2. Will the zone record just added auto add to the database as well? 3. Will it sync the database with the local ZoneFiles?
	01 Dec 2009 11:02 GMT - by Jesper - JH Software: Hi Mark, 1) No - we do not require a specific database table format. You can form a SELECT statement to fit whatever table/view/stored procedure format that you prefer. 2) Updates made to local zones (in "Records" window) do not affect plug-ins. 3) No - plug-ins and local zones / zone files are independent.
	07 Dec 2009 01:05 GMT - by Mark: What is the purpose of MS SQL plugin and this "Plus" plugin?
	08 Dec 2009 12:18 GMT - by Jesper - JH Software: Hi Mark, The purpose is to store and serve DNS records from a SQL database.
	08 Dec 2009 17:51 GMT - by Mark: I can see it can query DNS record from SQL database. However I cannot see it store anything to SQL. (zone file is created however) Do I miss something?
	08 Dec 2009 18:57 GMT - by Jesper - JH Software: Hi Mark, No it does not store (save) anything to SQL - it only reads. The idea is that you write to the database from some other application - for example a web-site.