Quickly add large numbers of users to a computer or server using addusers.exe

addusers.exe is a great tool that is included in Windows 2000 Resource Kit and is described by Microsoft as “32-bit administrative utility that uses a comma-delimited text file to create, modify, and delete user accounts”. The main benefit is using something like Excel or notepad to create a list of users, passwords and other details and then instantly generating their user accounts. We had to do this recently for almost 100 user accounts and it took seconds once we had written the text file. Although this isn’t officially supported on Windows 2003 or later, it still worked for us.

You can download addusers.exe direct from Microsoft’s FTP server or from Petri.il.

The command we used to import a list of users contained in “listofusers.txt” to our server “servername” using addusers.exe was:

addusers /C C:listofusers.txt \servername /P:LE

Where the text file listofusers.txt looked something like:

[User]
username1,User Fullname,p4ssword,email@somesite.com
username2,User2 Fullname,p4ssword2,email2@somesite.com
[Global]
[Local]

You can do a lot more with addusers.exe but this is a very handy and quick way of managing the creation of large numbers of users.

Easily migrate content between SharePoint servers using stsadm.exe and fix the HTTP 401.1 site collection administrator local login problem

I needed to migrate content from one install of SharePoint Web Services 3.0 on Windows 2003 to another physical server and ran into an issue with checking my site locally before changing all the DNS records over. The actual export and import process is quite easy but can take a while if there are a lot of subsites or files within your SharePoint portal.

Migrating between SharePoint installs using stsadm.exe

The easiest way to export an entire SharePoint site is to use stsadm.exe, which is typically located in “C:Program FilesCommon FilesMicrosoft Sharedweb server extensions12BIN” and is available when you have installed SharePoint. I created a reference to this in the PATH variable so I could use it everywhere to make things easier. Thankfully the documentation is good (thanks Microsoft) and you can find more explicit details on exporting and importing from TechNet. Note that this is similar to other versions of SharePoint and more information on various methods of migration can be found on Microsoft Support, including moving databases directly. Chris O’Brian also has a good post about the various approaches.

The simple stsadm command I used to export my portal (including all files and subsites etc) at http://portal.sitename.com was:

stsadm -o export -url http://portal.sitename.com -filename sitename.bak

This produced a lot of ~30mb files and a good log of everything that took place. All the Active Directory user permissions were also included in the export, which was one of my big worries moving to a new server. For local users you have more of a problem as these don’t exist on the new server and need to be recreated.

To import your site back into another SharePoint install, you have to first make sure there is an existing web application and associated site collection (on the root “/”) before copying over all your exported files to the new server. The first time I tried I assumed it would regenerate the site collection based on my export from scratch, but apparently not. The command I used to import was:

stsadm -o import -url http://portal.sitename.com -filename sitename.bak

Now even though you will probably have a lot of files from the export process, you do not need to specify them all, just the main one, in this case “sitename.bak”. After a while your new site will be populated with all the content from your export and is ready for testing before you go live and change your DNS records to point to the new server.

Testing your newly migrated site locally, avoiding HTTP 401.1

As I was using remote desktop to access my new server to run the stsadm.exe import command I wanted to test the site locally by logging in with my site collection administrator details before changing the DNS over. To do this I set up a reference in my hosts file “C:Windowssystem32driversetchosts” on the new server to point http://portal.sitename.com to localhost (127.0.0.1) then tried to visit http://portal.sitename.com within my remote desktop session. This is where I hit a HTTP 401.1 login error due to a security setting built into Windows 2003, even though I tried logging in with the correct site collection administrator details.

This is apparently a security fix to Windows Server 2003 SP1 that stops reflection attacks and according to Microsoft “authentication fails if the FQDN or the custom host header that you use does not match the local computer name”. The details on how to fix this are located at Microsoft Support and I’ve noted the easiest way to fix this by removing the loopback check entirely.

First you need to disable strict name checking by editing the registry on your server. Open the registry editor (run regedit.exe) and go to “HKEY_LOCAL_MACHINESystemCurrentControlSetServicesLanmanServerParameters”. Now click “Edit -> New -> DWORD Value” and name it “DisableStrictNameChecking” and then right click and set it to decimal “1″.

Now go to “HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlLsa” and click “Edit -> New -> DWORD Value” and name it “DisableLoopbackCheck” and then right click and set it to decimal “1″ as well.

You need to restart your server for the changes to have any effect and once you have you should be able to log in to your local site at http://portal.sitename.com without hitting a HTTP 401.1 error with your site collection administrator details. Now you can test out the site before changing the DNS records to point to the new server and removing your host file record.

Connecting to Microsoft SQL Server using ODBC from Ubuntu Server

Previously I showed how to connect to Microsoft SQL Server in PHP using mssql_connect(). This works but is not as neat as using ODBC.

Unfortunately when writing this guide I got stuck at the last hurdle when trying to use ODBC and adodb in PHP. I could connect to the database using:

include(‘adodb5/adodb.inc.php’);
$connection=& ADONewConnection(‘odbc_mssql’);
$connection->Connect(‘datasourcename’,’username’,’password’);

Or using odbc_connect:

$connection= odbc_connect(‘datasourcename’, ‘username’, ‘password’);

But selecting data using “$connection->GetAll()” or “odbc_exec()” just resulted in a page that never loaded. Fortunately the ODBC part worked, there was just something I haven’t figured out yet with ODBC in PHP.

This guide will show you how to set up ODBC in Ubuntu using FreeTDS to talk to Microsoft SQL Server. I am trying to work out why the server connects fine but won’t execute queries in PHP. When I find out why I will update this post. To create this guide I went off a post on Ubuntu forums with some additions. There are a lot of steps, but at least some of the programs you use along the way allow you to check things are working as expected before you come to test in PHP.

First you need to install php5-odbc in Ubuntu:

sudo apt-get install php5-odbc

Now you need to add freetds to create a connection and talk to your server, plus a few other handy programs:

sudo apt-get install unixodbc unixodbc-dev freetds-dev sqsh tdsodbc

Edit the example server details or create a new one in /etc/freetds/freetds.conf to point to your server (in my case I called it “sqlserver”):

[sqlserver]
host = ip.add.ress
port = 1433
tds version = 7.0

You can check the connection is set up using sqsh:

sqsh -S sqlserver -U username -P password

Alternatively, you can connect directly to the ip.add.ress:

sqsh -S ip.add.ress -U username -P password

Now in sqsh you can select from a table in your SQL Server database and display it on screen by entering your query and “go” on the next line:

1> select * from databasename.dbo.table
2> go

Hopefully you should see your data on screen. The next step is to configure ODBC so check where the config files are located using:

odbcinst -j

Which should say “DRIVERS: /etc/odbcinst.ini” and “SYSTEM DATA SOURCES: /etc/odbc.ini” which are the files we will be editing.

Now we set up the FreeTDS driver so it can be used by ODBC so open “/etc/odbcinst.ini” (was a blank file in my case) and add:

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
CPTimeout =
CPReuse =
FileUsage = 1

Open “/etc/odbc.ini” and create the system data source “datasourcename” that we will use everywhere (including in PHP):

[datasourcename]
Driver = FreeTDS
Description  = ODBC connection via FreeTDS
Trace = No
Servername = sqlserver
Database = databasename

It’s important to note that “Servername” here refers to the server details in /etc/freetds/freetds.conf NOT to an IP address.

You can test the connection works using isql:

isql -v datasourcename username password

Now you can use this data source “datasourcename” in PHP, which is the goal! To test the connection works I tried odbc_connect():

$connection = odbc_connect(‘datasourcename’, ‘username’, ‘password’);
if (!$connection) die(“Connection failed”);
else echo “works”;

Which does work. Now I just need to work out why queries such as the following cause PHP to hang:

$query = odbc_exec($connection, ‘SELECT column FROM table’) or die (odbc_errormsg());

Connecting to Microsoft SQL Server from PHP in Ubuntu using mssql_connect()

As part of ongoing testing to find the best way of storing large amounts of data we are considering Microsoft SQL Server, which needs to be accessible from our PHP5 application. The site is hosted on Ubuntu Server 10.10, which makes things a little more interesting. We currently use adodb5 to talk to a MySQL server, which works fine, apart from MySQL being far too slow. It is possible to create an ODBC connection in Ubuntu to talk to SQL Server but for testing the speed of our queries I just used mssql_connect(). In the near future I will move the database connection entirely to ODBC/adodb so we don’t have to rewrite any of our existing code.

Ubuntu doesn’t come with the packages needed for mssql_connect() by default so you need to install them:

sudo apt-get install php5-sybase

Then restart the apache server to apply the changes:

sudo /etc/init.d/apache2 restart

Now you can use mssql_connect and its associated functions in your PHP to connect to your Microsoft SQL Server, as in this example:

ini_set(‘display_errors’, 1);
$server = ‘my.server.ip:1433DATABASEINSTANCE’;
$link = mssql_connect($server, ‘username’, ‘password’);

if (!$link) {
die(‘<br/><br/>Something went wrong while connecting to MSSQL’);
}
else {
$selected = mssql_select_db(“databasename”, $link)
or die(“Couldn’t open database databasename”);
echo “connected to databasename<br/>”;

$result = mssql_query(“select name from table”);

while($row = mssql_fetch_array($result))
echo $row["name"] . “<br/>”;
}

Migrating data from MySQL to Microsoft SQL Server (and away from MySQL slowness)

I hit a stumbling block with MySQL that has made me seriously rethink using it in future. A simple JOIN query between two tables (one with 1k rows, 1 with 660k rows) was taking a very long time in MySQL. I had a look at indexing etc but couldn’t get the execution time down below 220 seconds on the test machine, no matter how the query was structured. I even considered adding redundant columns to the dataset to allow for indexing using integers, rather than varchars. While trying a few things I decided to test the exact same query on the same data set in Microsoft SQL Server Express 2005, where it took 4 seconds (1 second after the query had been cached).

The difference is obviously massive, far more than I was expecting. I then ran a few tests using DISTINCT, GROUP BY and COUNT(*) and noticed that in every instance, MySQL was slower than SQL Server, even on identical test machines. Considering the database is expected to grow massively over the next year we decided that it would be sensible to consider alternatives to MySQL. We already have a license for SQL Server so the decision was pretty easy (also, all my testing is currently done using SQL Server Express 2005).

Migrating the data across is not easy using .sql exports from MySQL Workbench as the data set is large (60mb of .sql) and the output file needs editing for SQL Server compatibility. Annoyingly SQL Server Express 2005 doesn’t support multiple INSERT VALUES queries (please upgrade to SQL Server 2008!) making it even more difficult.

I found a post on CodeProject by Niklas Henricson that explains how easy it is to migrate tables from MySQL to SQL Server using an ODBC connector from MySQL. You can download the ODBC connector from MySQL and create a connection which can then be easily used in SQL Server Management Studio to transfer data between MySQL and SQL Server. A summary of Niklas’ steps are:

  • Open your ODBC Data Source Administrator from the Control Panel -> Administrative Tools. Under the tab labelled as “System DSN”, press the “Add” button.
  • On the “Create New Data Source” dialog that appeared, choose MySQL ODBC 5.1 Driver and then press the “Finish” button.
  • After that, a MySQL connection configuration dialog will appear. Add your MySQL database account information in it, preferably the “root” account which has full access to your databases in MySQL. Do not change the port to anything other than 3306, unless during your MySQL server installation, you have defined something else.
  • Press the “Test” button to ensure your connection settings are set properly and then the “OK” button when you’re done.
  • In this state, you are ready to establish a link towards MySQL database from your Microsoft SQL Server Management Studio. Open a query window and run the following SQL statement:

EXEC master.dbo.sp_addlinkedserver
@server = N’MYSQL’,
@srvproduct=N’MySQL’,
@provider=N’MSDASQL’,
@provstr=N’DRIVER={MySQL ODBC 5.1 Driver}; SERVER=my.server.ip.or.name; _
DATABASE=databasename; USER=username; PASSWORD=password; OPTION=3′

  • In the query window, run the following SQL statement to import table tablename from the MySQL database databasename, into the database in Microsoft SQL called SQLServerdatabasename.

SELECT * INTO SQLServerdatabasename.dbo.tablename
FROM openquery(MYSQL, ‘SELECT * FROM databasename.tablename’)

The potential for ODBC based migration of data is brilliant and openquery can be scripted to run across all the database tables. I am currently looking at a way of bulk importing an entire database from MySQL to SQL Server and will update this post in the future.

There are a few things to be wary of, such as keys, data types, creation of identity specifications etc.. but this can all be sorted once the data is migrated across. The speed is pretty good and shouldn’t be too much of a concern as this is a one time operation.

Speech Recognition Using Microsoft SAPI

Although this may be a bit out of date considering the Microsoft Speech SDK is out and has been for a while, the method used in this CodeProject article is a great starting point. We are using it to allow people to update a speech recognition grammar using a website. It is actually pretty easy to do this and makes a lot of sense compared to file based grammars if you are just dealing with simple phrases.

The great thing about SAPI is that it’s so easy to implement in any .net project (when you know how of course). We are currently using a database to store our phrases and then updating the grammar on any change to the database using regular polling. This isn’t ideal but is pretty quick & we need to regularly touch the database for other reasons.