Category Archives: SQL

Moving ParaPlan database to new server

Leave a reply

Over time, servers get old and worn out and need to be replaced with new servers. This can be a scary and often delayed task due to the difficulty of moving data to the new server. Some software has extensive configuration that can make the upgrade process difficult. Not so with ParaPlan! In fact, we have all the tools built into the software that are need to move to a new server. It is literally a 3 step process. 

  1. In ParaPlan, click All Elements -> Admin to launch the admin window. When it loads, expand Support and click “Send Database to EnGraph”.

    Pic1

  2. Name your new database, expand Select Destination and populate the connection information for the new server. Then click Send Database. This will take 5 – 20 minutes based on the size of the database.

    Step2

  3. On each workstation, click All Elements -> About ParaPlan, then Manage Profile. On the window that opens up, select the Database Info tab and populate the connection information for the new server. Click Save, then  restart ParaPlan. It is now connected to the new server.

    Profile

 

This article explained how quickly and easily our clients can move their data to a new server. For more information about ParaPlan paratransit dispatching software or EnGraph, please visit our website or our Facebook page.

Standing up a SQL Server for free on Amazon EC2

Leave a reply

Amazon EC2 provides free micro instances of Windows 2008 x64 with SQL and IIS preinstalled. I’ve been carving out pieces of our internal infrastructure and placing them into cloud-based solutions like Amazon. Ideally, a bomb could go off at EnGraph and we wouldn’t skip a beat.

Since the process of configuring Microsoft SQL Server is easy, but not exactly obvious, I’ve laid out step-by-step instructions on how to do this. Did I mention it is free? Well, 750 hours a month for free (I’ll wait while you do the math).

  1. Sign into Amazon Web Services console. If this is the first time working with AWS, credit card authorization ($1.00) and a phone call verification will be performed. From scratch, this process takes about 10 minutes to complete.
  2. Once the console is loaded , click “Launch Instance”.
  3. Select Windows 2008 x64 with SQL Express and IIS and click “Select”.
  4. On the following screen, make sure “micro” is selected and click “Continue”.
  5. On the Advance Instance Detail page, make sure shutdown behavior is “stop”, click “Continue”.
  6. A name can be entered on the Keys page to distinguish it from other servers, then click “Continue”.
  7. Creating a key pair will allow Remote Desktop Access to the EC2 instance. This is needed. Name the key, then click “Create and download”. Download the .pem file to a place where it will not be lost. You are using Dropbox, right? Click “Continue”.
  8. The Security Group page configures which ports will allow incoming traffic. It is like the external firewall. By default ports 80 (IIS), 3389 (RDP) and 1433 (SQL) are allowed. Create a new security group with these default settings and click “Continue”.
  9. Verify all the settings are correct on the last page. Pay specific attention to Instance Type (should be micro) Shutdown behavior (should be stop) and key pair name (should match what was created in step 7). Click “Launch”.
  10. The server will be created and configured, then it will spin up and be ready for use. This process usually takes about 10 – 15 minutes.
  11. Once the server is ready, check the box next to the new server name, then click “Instance Management”, when the context menu opens, click “Get Windows Password”.
  12. On the screen that opens up, click “Choose File” and browse to where the .pem file was saved from step 7. This will decrypt the administrator password. Copy and paste this password in a new note. You are using Evernote, right?
  13. Back in the Management Console, click “Elastic IPs” under Network & Security.
  14. Click “Allocate New Address”
  15. On the dialog that opens up, select “EC2″ and click “Yes, Allocate”
  16. A new IP address will be allocated and displayed in the list of address. Make note of the new address and copy it into your Evernote note.
  17. Check the box next to the new IP address, then press “Associate Address”
  18. On the dialog that opens up, select the new instance and press “Yes, Associate”
  19. Open a Remote Desktop Client – I’m partial to CoRD.
  20. Create a new RDP connection. The server name will be the address from step 16. The username will be administrator and the password will be from step 12. Go ahead and forward disks as well.
  21. Once an RDP connection is established with the new server (pretty cool right? Remember this is free!), go ahead and change the administrative password to something easier to remember. Click the Start menu, select Control Panel, click User Accounts and step through the wizard to change the password – make note of the updated password.
  22. Log off the server and reconnect using the new password.
  23. Internet Explorer is a nightmare on a server OS. I keep a standalone installer of Google Chrome in my Dropbox. This is why we forwarded the disks in step 20. Open up My Computer and browse to the installer file using the \\tsclient drives. This step is not relevant to this exercise, but it is just good practice.
  24. We are now ready to configure SQL Server. Click the start menu and browse to SQL Server Management Studio.
  25. Once it loads (it will take a little bit the first time), logon using the credentials that are pre-populated.
  26. Right click on the server name (at the top of the tree view) and select “Properties”. A dialog box will open up.
  27. Click the security tab and change the Server authentication to be “SQL Server and Windows Authentication mode”. This is also know as mixed mode. Click “OK” to close the dialog and press “OK” to the next dialog telling you to restart the server (it doesn’t mean the entire server, just the service running SQL Server – not exactly intuitive naming in my opinion).
  28. Right click on the server name and select “Restart”, click “Yes” to the “are you sure” prompt.
  29. Once the SQL Server is restarted, expand the “Security” node, then expand the “Logins” node. Right click “sa” and select properties.
  30. Create and confirm a new password. Then click into the “Status” pane and change it to  “Enabled” under “Login”. Click “OK”.
  31. At this point, Management Studio should be closed and reopened using the new credentials for testing. Change the Authentication mode to “SQL Server” and enter “sa” for the user and the password created in step 30.

We are finished! Management Studio can be opened up from a different computer, enter the address from step 16 and control the SQL Server instance remotely.

A couple things to remember.

  • There is only about 9 or 10 GB free after everything is installed and configured. This should not be used for Wikipedia clones.
  • There is only 613 MB of RAM. Make sure to log off Windows after finishing RDP sessions to conserve resources.

I’ve been really happy with my micro EC2 instances. This blog is run on a micro instance (and has stood up to some large traffic days), I’m slowing moving MentionNotifier to run on a combination of EC2 and SES.

We also have a large instance running that we use for our ParaPlan Cloud clients. It has been a great experience for us. We are able to save our clients a lot of money in IT and server costs, while not having to actually maintain any hardware.

I hope this tutorial has been helpful. Cloud computing is the way of the future and it is very nice to see companies like Amazon lower the barrier of entry so far that people can try it out for free.

Is SQL Server secure?

5 Replies

I’ve been using SQL Server since I first learned how a database works. My first big project at EnGraph was converting our database from a Access backend to a MSSQL one. I’ve learned quite a bit over the years and was actually quite taken aback when asked by a client if SQL Server was secure or not. Specifically, they were asking about our ParaPlan Cloud product that we host using Amazon EC2 and were concerned about opening up port 1433.

I was taken aback partly because my thought process was “of course SQL Server is secure! How silly is it to question that?”, but mostly because after working with SQL Server for over a decade, I didn’t have a good answer that would satisfy their concerns. We have an entire company built around this product, we should probably understand how the security works. So I told them I’d do some research and get back with them.

So after several hours of research, this is what I have learned:

Username/password transactions are always encrypted (MSDN):

Credentials (in the login packet) that are transmitted when a client application connects to SQL Server are always encrypted. SQL Server will use a certificate from a trusted certification authority if available. If a trusted certificate is not installed, SQL Server will generate a self-signed certificate when the instance is started, and use the self-signed certificate to encrypt the credentials.

You can do additional encryption on the database/tables/columns, but at at the price of performance (Pinal Dave):

Encryption is a very important security feature of SQL Server 2005. Long keys and asymmetric keys create unassailable, stronger encryption and stronger encryption uses lots of CPU to encrypt data. Stronger encryption is slower to process. When there is a huge amount of data to encrypt, it is suggested to encrypt it using a symmetric key. The same symmetric key can be encrypted further with an asymmetric key for additional protection, thereby adding the advantage of a stronger encryption. It is also recommended to compress data before encryption, as encrypted data cannot be compressed.

If you want to further control outgoing SQL access, you can block all outgoing traffic on port 1433, except to specific servers. Here is how it can be done on with a Cisco.

I now have much more knowledge about SQL Server security and feel more comfortable providing our client with this information and helping them decide to go with our self-hosted, or EnGraph hosted product.