SQL Permissions Script

Running the following sql permissions script against your sql databases helps protect those databases and your sql server as a whole from sql injection exploits.

DECLARE @LOGIN varchar(255)
DECLARE @PASS varchar(255)
DECLARE @DB varchar(255)

SELECT @LOGIN = 'yourdbuser'
SELECT @PASS = 'yourdbpass'
SELECT @DB = 'yourdb'

/* set default database */
EXEC sp_defaultdb @LOGIN, @DB

/* drop system admin role */
EXEC sp_dropsrvrolemember @LOGIN, 'sysadmin'

/* drop database owner role */
EXEC sp_droprolemember 'db_owner', @LOGIN

/* grant execute on all non system stored procedures and scalar functions */
DECLARE @SP varchar(255)
DECLARE Proc_Cursor CURSOR FOR
SELECT name FROM sysobjects
WHERE (type='P' or type='FN')
AND category <> 2 -- system
OPEN Proc_Cursor
FETCH NEXT FROM Proc_Cursor INTO @SP
WHILE(@@FETCH_STATUS=0)
BEGIN
EXEC ('GRANT EXECUTE ON ['+@SP+'] TO ['+@LOGIN+']')
FETCH NEXT FROM Proc_Cursor INTO @SP
END
CLOSE Proc_Cursor
DEALLOCATE Proc_Cursor

/* grant select on table functions */
DECLARE @TF varchar(255)
DECLARE Tf_Cursor CURSOR FOR
SELECT name FROM sysobjects
WHERE (type='TF')
AND category <> 2 -- system
OPEN Tf_Cursor
FETCH NEXT FROM Tf_Cursor INTO @TF
WHILE(@@FETCH_STATUS=0)
BEGIN
EXEC ('GRANT SELECT ON ['+@TF+'] TO ['+@LOGIN+']')
FETCH NEXT FROM Tf_Cursor INTO @TF
END
CLOSE Tf_Cursor
DEALLOCATE Tf_Cursor

/* grant select/update/insert/delete on all user defined tables */
DECLARE @T varchar(255)
DECLARE Table_Cursor CURSOR FOR
SELECT name FROM sysobjects
WHERE (type='U' or type='V') -- user defined tables and views
OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor INTO @T
WHILE(@@FETCH_STATUS=0)
BEGIN
EXEC ('GRANT SELECT, UPDATE, INSERT, DELETE ON ['+@T+'] TO ['+@LOGIN+']')
FETCH NEXT FROM Table_Cursor INTO @T
END
CLOSE Table_Cursor
DEALLOCATE Table_Cursor

/* deny access to system tables */
EXEC('DENY SELECT ON syscolumns TO '+@LOGIN)
EXEC('DENY SELECT ON sysobjects TO '+@LOGIN)

EXEC('DENY SELECT ON sys.columns TO '+@LOGIN)
EXEC('DENY SELECT ON sys.all_columns TO '+@LOGIN)
EXEC('DENY SELECT ON sys.all_objects TO '+@LOGIN)
EXEC('DENY SELECT ON sys.all_parameters TO '+@LOGIN)
EXEC('DENY SELECT ON sys.all_views TO '+@LOGIN)

/* Corrects user mapping issues when restoring a database */
EXEC sp_change_users_login 'Auto_Fix', @LOGIN, NULL, @PASS;

Asp classic connection to azure sql server

This is the connection string needed to connect to a database on a SQL Server instance within a Windows VM on Azure.

Set Con = server.createobject("ADODB.Connection")
Con.Open "Provider=SQLOLEDB;Server=AZURE-VM-NAME.cloudapp.net,AZURE-VM-PORT;User ID=DATABASE-USERNAME;Password=DATABASE-PASSWORD;Initial Catalog=DATABASE-NAME;"

Creating an IsNumeric Function for Asp.Net C#

In VB there is a built-in isNumeric() Function. For whatever reason Microsoft didn’t include such a function for C#. It’s easy enough to create yourself a handy function that you can include in your projects.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    namespace WBSystems
    
    {
        public class Functions
        {
            // IsNumeric Function
            public static bool IsNumeric(object Expression)
            {
                // Variable to collect the Return value of the TryParse method.
                bool isNum;
                
                // Define variable to collect out parameter of the TryParse method. If the conversion fails, the out parameter is zero.
                double retNum;

                // The TryParse method converts a string in a specified style and culture-specific format to its double-precision floating point number equivalent.
                // The TryParse method does not generate an exception if the conversion fails. If the conversion passes, True is returned. If it does not, False is returned.
                isNum = Double.TryParse(Convert.ToString(Expression), System.Globalization.NumberStyles.Any, System.Globalization.NumberFormatInfo.InvariantInfo, out retNum);
                return isNum;
            }
        }
    }

Connecting to Access and Excel on Windows Server or Windows 7 where there is a mismatch between 32bit and 64bit versions of software and hardware

sample_excelsheet

I recently needed to connect to an Excel spreadsheet from an ASP Classic script from a site running on 64bit Windows 7 and IIS7.

This isn’t as straightforward as it might seem. Why? Because Windows 7 doesn’t include the necessary drivers as standard. Also if you have Office or a component of Office installed, on a developer machine for example, this causes a problem as, for the most part, Office installations are 32bit.

You need to download the Microsoft Access Database Engine 2010 Redistributable from the Microsoft download center. A 32bit driver and 64bit driver are both available.

What works?

If you have a 64bit server with no Office installation then install the 64bit driver.

If you have a 64bit server with 32bit office installation then install the 32bit driver*.

If you have a 32bit server with no Office installation then install the 32bit driver.

If you have a 32bit server with 32bit Office installation then install the 32bit driver.

*If you are trying to install the 32bit drivers on a 64bit machine then the installer will complain and fail. However the install has an override flag if you install it via the command line (cmd.exe) with administrator privileges.

AccessDatabaseEngine_x64.exe /passive

ASP Classic Connection Strings

Access File

<%
Set Con = Server.CreateObject("ADODB.Connection")
Con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\temp\Database1.mdb;Persist Security Info=False;"
Con.Close
%>

Excel File

<%
Set Con = Server.CreateObject("ADODB.Connection")
Con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\temp\book1.xlsx;Extended Properties=Excel 12.0;"
Con.Close
%>

Download both drivers from microsoft download center

Where does MySQL store databases running on Windows Server?

MySQL stores configuration information within a file called my.ini which is stored within the MySQL installation folder. This can vary but typically is located within the MySQL folder in Program Files, e.g.

C:\Program Files\MySQL\MySQL Server 5.1\my.ini

Once located open the file in notepad or another such text viewer. Then look for the datadir parameter. This details the location of your database files, e.g.

basedir=&quot;C:/Program Files/MySQL/MySQL Server 5.1/&quot; #Path to the database root
datadir=&quot;C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/Data/&quot;

Azure Websites and .less CSS files

I recently had an issue getting a .less CSS file to work on a website hosted on the Windows Azure platform. The file would keep coming back as a 404 not found error. After a bit of searching I found that Azure don’t currently register .less as a valid file mimetype.

The solution I used was to add the mimetype manually to the site’s web.config file. This even works for code like WordPress running on Azure under php.

<configuration>
    <system.webServer>
        <staticContent>
            <mimeMap fileExtension=".less" mimeType="text/css">
        </staticContent>
    </system.webServer>
</configuration>

Firefox Sync encountered an error while syncing

I recently had an issue where my bookmarks in Firefox suddenly and completely disappeared.

firefox-sync-error-1

Firefox sync then started to report the following error whilst syncing manually or during its scheduled sync:

Sync encountered an error while syncing: Unknown error. Sync will automatically retry this action.

firefox-sync-error-2

After viewing the sync log files and doing various searches I didn’t come up with any solutions that worked.

My Solution:

With the Firefox browser completely closed I browsed to the Firefox profile I was using and looked for a file called places.sqllite.

C:\Users\MyWindowsUser\AppData\Roaming\Mozilla\Firefox\Profiles\MyFirefoxProfile.default

I then rename this file -> places.sqlite.corrupt

firefox-sync-error-3

Then opened up Firefox browser as normal. Upon opening Firefox detects that this file is missing and recreates it. This results in an empty bookmark bar but within seconds Firefox sync successfully reloaded my bookmarks from the Sync database.