Thursday, August 17, 2017

Connecting to MySQL Server from a Microsoft SQL Server

This seams to me that connecting MySQL and Microsoft SQL databases is something that should be simple to do. Unfortunately looking around online there was no real clear directions or even indications that this was possible without buying some additional drivers.  While fear not because this does work out of the box without needing to buy any additional software on pretty much any versions of the two databases.

In short what we will do is add a MySQL ODBC driver, configure a System DSN using that driver, Link MsSQL to that DSN connection, and to do a few test queries and updates.  This will allow full insert, update, and delete commands.

You will need administrative access to the Microsoft SQL Server along with access to the database. In addition you will need to know the connection information for the MySQL database, it's IP Address, a list of the databases you want to connect, and any MySQL login's and passwords you will be using.

Setup

First off you will need to identify if you have the 32-bit or 64-bit version of Microsoft SQL Server.  You can determine this by connecting to the server in Management Studio, Right Clicking on the connected server, selecting Properties, and checking the Product field.

Once you have determined the proper edition go here and download the matching format of the MySQL ODBC driver. You do not need to create and account as there is a link to skip that step. Be sure to install this package.

ODBC Configuration

We need to create one or more ODBC connections using this new driver called DSN's. Navigate to the Administrative Tools folder in your start menu or control panel and launch the matching 32-bit or 64-bit version of ODBC Data Source.

As for how to set up the connections you have a few options.  You can create individual DSN's for each database using separate user logins for each, you can create one DSN with permission to all the tables, or you can create one DSN and map MsSQL users to MySQL users and control what database's each can see based on that mapping. Decide on this and then adjust the Server Name and Database Name fields below as you configure things.

If you are using one login for everything or mapping users across then do the following.
  • Open the ODBC Data Source Administration tool.
  • Go to the System DSN tab.
  • Click Add to create a new ODBC connection.
  • Select MySQL ODBC 5.3 ANSI Driver or  MySQL ODBC 5.3 Unicode Driver as your needs dictate. Unicode supports a larger character set.
  • Click Finish.
  • On the new screen fill in the following fields:
    • Data Source Name: ServerName
    • TCP/IP: MySQL Server IP Address
    • User/Password: MySQL Admin username/password.
    • Database: Leave this blank.
  • Click Test to make sure it is connection and then press Ok to save.

Or if you are creating separate ODBC connections for each database or only have one database but use one login per database then do this instead for each.
  • Open the ODBC Data Source Administration tool.
  • Go to the System DSN tab.
  • Click Add to create a new ODBC connection.
  • Select MySQL ODBC 5.3 ANSI Driver or  MySQL ODBC 5.3 Unicode Driver as your needs dictate. Unicode supports a larger character set.
  • Click Finish.
  • On the new screen fill in the following fields:
    • Data Source NameServerName/DatabaseName
    • TCP/IP: MySQL Server IP Address
    • User/Password: MySQL login/password with access to the specific database.
    • Database: Select the proper database.
  • Click Test to make sure it is connection and then press Ok to save.

Microsoft SQL Server Configuration

Now for the final step open up the Microsoft SQL Server Management Studio and connect to the database server.  Agains he steps will vary depending if you have one single connection are are making connections for each database in question. If you are using one login for everything to grand full access to any MsSQL users to all MySQL data do the following.
  • Navigate to Server Objects -> Linked Servers then Right Click -> New Linked Server.
  • Fill in the following on the General Tab:
    • Linked Server: ServerName
    • Provider: Microsoft OLE DB Provider for ODBC Drivers
    • Data SourceServerName
  • On the Security Tab:
    • Select Be made using the security context.
    • Reneter the same MySQL Admin username/password used above.
  • Click Ok
    • If there are any issues select No and make adjustments.  Once you click Yes you can't edit the General settings and have to remove and re-add the connection.

On the other hand if you want to map MsSQL Users to MySQL users so they have access to different tables based on the login then so the following.
  • Navigate to Server Objects -> Linked Servers then Right Click -> New Linked Server.
  • Fill in the following on the General Tab:
    • Linked ServerServerName
    • Provider: Microsoft OLE DB Provider for ODBC Drivers
    • Data SourceServerName
  • On the Security Tab:
    • Add a row for each user that should be mapped and select the MySQL Local Login Name.
    • Enter the matching MySQL usernames and passwords or shared account details that each login should map to.
    • Leave Impersonate unchecked.
    • You can change the radio to Not be made so that unmapped users don't see anything or specify a general login for unmapped users.
  • Click Ok
    • If there are any issues select No and make adjustments.  Once you click Yes you can't edit the General settings and have to remove and re-add the connection.

Or if you create per database DSN's then to the this for each.
  • Navigate to Server Objects -> Linked Servers then Right Click -> New Linked Server.
  • Fill in the following on the General Tab:
    • Linked ServerServerName/DatabaseName
    • Provider: Microsoft OLE DB Provider for ODBC Drivers
    • Data SourceServerName/DatabaseName
  • On the Security Tab:
    • Select Be made using the security context.
    • Reneter the table specific MySQL username/password for this table.
  • Click Ok
    • If there are any issues select No and make adjustments.  Once you click Yes you can't edit the General settings and have to remove and re-add the connection.

Querying and Updating Data

Now that this is connected you can browse the structure in Management Studio by going back to Server Objects -> Linked Servers -> Server Name.  To test some sample operations lets assume we have the following table in MySQL.
  • test01
    • id, int, pk, auto_increment
    • Name, nvarchar(20) not null
    • Items, smallint, not null
Then we can do all of the following:
SELECT count(*) as 'Count'
FROM OPENQUERY([ServerName/DatabaseName], 'SELECT * FROM test01')
GO

INSERT OPENQUERY([ServerName/DatabaseName], 'SELECT Name, Items FROM test01 ')
VALUES
('Shirts', 47),
('Pants', 55),
('Shorts', 2),
('Shoes', 43)
GO

SELECT *
FROM OPENQUERY([ServerName/DatabaseName], 'SELECT * FROM test01')
GO

UPDATE OPENQUERY([ServerName/DatabaseName], 'SELECT * FROM test01 WHERE Name = ''Shoes'' ')
SET Items = 55
GO

SELECT *
FROM OPENQUERY([ServerName/DatabaseName], 'SELECT * FROM test01  WHERE Name = ''Shoes''')
GO

DELETE OPENQUERY ([ServerName/DatabaseName], 'SELECT id FROM test01 WHERE Items > 50')
GO

SELECT *
FROM OPENQUERY([ServerName/DatabaseName], 'SELECT * FROM test01')
GO

Friday, July 21, 2017

Netflix to Trakt.tv Sync

There used to be a nice Chrome plugin to synchronize by Netflix history with Trakt.tv but it had been unreliable and doesn't seam to be working now.  I wrote this code to solve my own need but it does the same thing and I am posting it here for others to use.

Note that this is not 100% perfect as some show names aren't exactly the same and some Netflix show's don't use episode names at all. This code it not using any Netflix API calls but rather just scraping your Viewing Activity page and then making API calls to your Trakt.tv account to post the item's it finds.

You may need to manually select some episodes or shows on the Tract.tv side if they aren't matched and it is possible that duplicates could get synced if the names cause issues though I tried to prevent this. In short I'm not out to make this 100% perfect but it should get you 90% of the way and it shows all the changes it will post for confirmation before posting.

Usage

  • Drag this link, Sync to Trakt.tv, to your bookmark bar or bookmerks menu.
    • This is a bit of Javascript that will inject the Sync tool into your Netflix activity page.
  • Open Netflix in your browser and log in.
    • This is tested and working in Safari and Chrome.
  • Go here https://www.netflix.com/viewingactivity to see your history or click on your name in the top right, then on Account, and finally on Viewing Activity.
  • Netflix will not load your entire history so you will need to scroll down to get it to load more.  Keep doing this until all the history you want to sync has been loaded.
    • You don't have to do everything, if you are doing this regularly then the first screen or two will be sufficient.
  • Now for the magic click the bookmarklet you added while looking at the history page.
    • Note: The first tim you do this you will need to log in to Trakt.tv and click the bookmark again.
  • The script will scrape the Netflix page, they check each item in Trakt.tv and build a list of already synced items, items not found, and items scheduled to sync.
    • If re-running the tool past plays will be greyed out, new plays to by synced will be green,  items that could not be cached will remain white, and duplicate plays that are being ignored will be orange.
  • When you are satisfied just click Sync Now to post the changes.  You can then check Trakt.tv or reload the page and run the script again to see the results.

How It Works

This solution is a simple bit of javascript that scans your Netflix history page and sync's plays over to Trakt.tv. It also needs to read your list of past plays in Trakt.tv along with the history it reads from the Netflix page.

None of this data is saved or sent to any other servers in any way. As the script runs a listing of all the changes that will be posted to Trakt.tv is show with a confirmation option. No passwords or logins are prompted or recorded in any way. You are logging in yourself to Netflix and Trakt.tv, though you do have to grant this application API access to your account. The only bit that is saved is the Trakt.tv access token so that you don't have to log in on every use of the tool. You can even remove this by clearing the cookies for the www.netflix.com domain.

You can view the entire script here first if you wish. The bookmarklet code is an easy way to inject this code into your netflix page, you could also run this code manually in your browser console.

function load(filename){
 if(filename.endsWith('.js')){
  var fileref=document.createElement('script');
  fileref.setAttribute('type','text/javascript');
  fileref.setAttribute('src',filename);
 }
 else if (filename.endsWith('.css')){
  var fileref=document.createElement('link');
  fileref.setAttribute('rel','stylesheet');
  fileref.setAttribute('type','text/css');
  fileref.setAttribute('href',filename);
 }
 document.getElementsByTagName('head')[0].appendChild(fileref);
}
load('https://code.jquery.com/ui/1.12.1/jquery-ui.js', 'js');
load('https://www.inkonit.com/netflix/netflix-sync.js','js');
load('https://www.inkonit.com/netflix/netflix-sync.css','css');

Monday, June 19, 2017

Restricting Access at in Varnish Server Wide to Admin Areas

There are lots of ways to implement access controls and additional security on your hosting. I recently implemented a very restrictive but easy to manage implementation for our FreeBSD server that runs everything through Varnish. I could have placed this restriction in Apache via .HTACCESS files or global rules but placing them in Varnish they execute earlier and have less server overhead and ensure there are no caching related flaws.

To set this up I added the following to the top of my /usr/local/etc/varnish.vcl file.
# Whitelist of IP Address's or Ranges that are allowd to access restricted administration pages.
acl admin {
    "127.0.0.1";
    "localhost";
    "10.1.0.0"/16; # Local Network Class B
    "192.168.1.0"/16; # Local Network Class C
    "1.1.1.1"; # Sample
    # INSERT IPS #
}

Next inside the vcl_recv sub, or add one if needed include this before other rules. The bold text is doing URL matching to determine what scripts or directories to require approval for.  This could also be applied to specific domains but in this case it is server wide.
# Optional feature to only allow access to matched pages if client is on a whitelist.
    if (req.url ~ "^/wp-(login|admin|cron|json)" && client.ip !~ admin) {
        # Whitelist for all admin pages
        return (synth(403, "IP address not authorized, please request access from AdminEmail" ));
    }

Now you can reload varnish and do some testing by adding and removing your local network or IP and ensure its working. You can stop here but this isn't very convenient to manage or update form offsite.
sudo service varnishd reload

Add a custom script to grant access to IP's.

We have a custom script hidden our our server that it not indexed or published anywhere that our administrator can use to add new addressed on the fly. It asks for a name, location, and IP Address and will update the varnish.vcl file and reload the configuration when submitted.

You can post this script to a secret location on the web server.  You may want to add a password to it for extra security and don't link to it anywhere. You will also need to run the following commands.

// Make writable by www user.
sudo chmod g+w /usr/local/etc/varnish.vcl
sudo chgrp www /usr/local/etc/varnish.vcl
// Add custom script to reload varnish.
echo "#!/usr/local/bin/bash
service varnishd reload" > ~/varnish-reload
// Move script and make it executable
sudo cp ~/varnish-reload /usr/local/bin/varnish-reload
rm ~/varnish-reload
sudo chmod +x /usr/local/bin/varnish-reload

Read XML Sitemap to a CSV File

You may need to process a sitemap for redirects or get a list of all your pages for some testing or monitoring process.  Here is a simple script you can run to convert your sitemap to a CSV file.  It even supports multi-file sitemaps.

Simply save this script sitemap-to-csv.pl to your computer. You will need Perl to run in.

Then you can execute it from the command line with the following format:
// This will automatically append /sitemap.xml
./sitemap-to-csv.pl http://domain.com

// Or specify the exact file.
perl sitemap-to-csv.pl http://domain.com

// With SSL domain authentication override.
PERL_LWP_SSL_VERIFY_HOSTNAME=0 ./sitemap-to-csv.pl http://domain.com

Monday, February 6, 2017

Configure SSL Termination with Varnish Caching and HTTP/2

To create the fastest web pages possible in a LAMP stack can be a bit tricky.  This document covers configureing blazing fast HTTPS sites on Freebsd with Apache, Varnish, Nginx, PHP, Mysql and letsencrypt.sh.  Of course you can swap out the PHP/Mysql parts as needed, or even use your own certificates instead of lets encrypt.sh as you need.

Monday, January 30, 2017

Easily post leads in Wordpress Fast Secure Contact Form Plugin to iContact

When using the Fast Secure Contact Form plugin for wordpress is is very very simply to seamlessly forward leads from any forms you wish to the iContact platform with very little configuration.