Connecting to Microsoft SQL Server using jTDS

Here’s how we are able to connect to Microsoft SQL Server using the jTDS JDBC driver. This is a local development environment using a newly installed SQL Server Express 2012.

1. Create the user with SQL Server authentication. Here we are using SQL Server Management Studio to create the new user “demo”.

Microsoft SQL server Login

Figure 1

Connecting to Microsoft SQL Server using jTDS - Fig 2

Figure 2

2. Make sure SQL Server supports both Windows and SQL Server authentication. Here we are using SQL Server Management Studio.

Connecting to Microsoft SQL Server using jTDS - Figure 3

Figure 3

Connecting to Microsoft SQL Server using jTDS - Figure 4

Figure 4

3. Enable TCP/IP for the database instance. Here we are going to use the SQL Server Configuration Manager.

Connecting to Microsoft SQL Server using jTDS - Figure 5

Figure 5

Connecting to Microsoft SQL Server using jTDS - Figure 6

Figure 6

4. Start the SQL Browser service. Go to Control Panel -> Administrative Tools -> Services and start the SQL Server Browser.

Connecting to Microsoft SQL Server using jTDS - Figure 7

5. Here are the JDBC parameters passed to jTDS.

url=jdbc:jtds:sqlserver://localhost:1433/MY_DATABASE;instance=MY_INSTANCE
driver=net.sourceforge.jtds.jdbc.Driver
user=MY_USERNAME
password=MY_PASSWORD

Please replace the MY_ variables with your own values.


Automated MySQL Backup

Here’s one way to create an automated MySQL backup process.

1. Login as root.

2. Create the MySQL option file /root/.my.cnf with the following contents. This will allow you to run mysqldump without passing the password parameter.

	[client]
	password=YOUR_MYSQL_PASSWORD
	Commands:
	> vi /root/.my.cnf

3. Protect your MySQL password by limiting access to /root/.my.cnf.

	Commands:
	> chmod 600 /root/.my.cnf

4. Create a backup script under cron.daily with the following contents. This will create a compressed database dump. The filename will contain the day of the week, which will automatically handle backup rotation.

	mysqldump --defaults-extra-file=/root/.my.cnf -u YOUR_MYSQL_USERNAME YOUR_MYSQL_DATABASE > /root/backup/db/mysql-`date +%a`.sql
	bzip2 -f /root/backup/db/mysql-`date +%a`.sql
	Commands:
	> cd /etc/cron.daily
	> vi mysqlbackup.cron
	> chmod u+x mysqlbackup.cron

Cannot Connect Remotely to a New MySQL Installation

We recently installed MySQL on an Ubuntu box for testing purposes. Setup was done by simply running the following as root.

sudo apt-get install mysql-server

Here’s what we did so we could connect remotely from a Windows 7 box.

1. Run mysql as root and grant root@% access.

# mysql -u root
mysql> grant all on *.* to 'root'@'%' identified by password '';

Here we keep using the default root user with no password.

2. Edit my.cnf and allow MySQL to listen to all network interfaces (not just 127.0.0.1). Restart MySQL for the changes to take effect.

# sudo vi /etc/mysql/my.cnf
bind-address = 0.0.0.0
# sudo service mysql restart

3. Once you can connect, we suggest you add the password. The command looks something like:

mysql> set password for 'root'@'%' = PASSWORD('secret');