Category: Microsoft

Easy guide to configuring Windows 2008 Firewall for SQL 2008 R2

After installing SQL Server 2008 R2 on a Windows 2008 server you will need to configure the firewall (if it’s turned on!) to allow access to the SQL server. Here is the simple method using the interface and assumes the following:

  • The SQL Instance is the default (first installed)
  • The ports have not been manually configured
  • Nothing too exotic like database mirroring is required.
  1. Open the Firewall either from the Control panel

    or by running WF.msc
  2. There are 3 default firewall profiles. One or more can be active depending on what and how you have configured your NICs. You will need to ensure you are editing the correct profile. Here you can see that mine is on a domain, so my Domain Profile is Active.
  3. Select ‘Inbound Rules’ in the left hand pane.
  4. Click on ‘New Rule…’ in the right actions pane.
  5. select Port Rule Type and click Next>
  6. Set TCP and Specific local Ports to 1433 and click Next>
  7. Leave the default of ‘Allow the connection’ and click Next>
  8. If you don’t ever want SQL to be directly accessible on a Public network (assuming you are going to ever connect your SQL server directly to the internet for some reason!!) then un-check the ‘Public’. Otherwise just click Next>
  9. Name it SQLPort and give it a description and click Finish.

That will enable inbound connections to your SQL Server. If you are also enabling SQL Browser Service then you will need to add UDP Port 1434 too another inbound rule.

If you have more than one instance of SQL then you will need to set the port and create rules for them.

For full details on ports and setting it up go to http://msdn.microsoft.com/en-us/library/cc646023(v=SQL.105).aspx

SQL script to kill all connections to a SQL Server database

I have finally perfected a nice script for killing connections to databases. Although basic, I found many of the ones I google’d, had one problem or another that stopped them working straight away, which just wastes time. This one works! I have just used it.

Just replace the database name and it’s good. It is easy to alter it to work across a list of databases too.

-- =============================================
-- Author: Nicholas Rogoff
-- Create date: 04/03/2010
-- Description: Script that Kills all connections to a database
-- except the current one
-- =============================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DECLARE @execSql NVARCHAR(1000)
DECLARE @databaseName VARCHAR(100)
DECLARE @NoKilled INT

-- *** CHANGE THE NAME OF THE DATABASE *** ---
SET @databaseName = '#### Replace this with a database name here ####'
PRINT 'START: Killing active connections to the "' + @databaseName + '" database'

-- Count Connections
select @NoKilled = COUNT(*)
from master.dbo.sysprocesses
where db_name(dbid) = @databaseName
 and
 DBID <> 0
 and
 spid <> @@spid

-- Create the sql to kill the active database connections
set @execSql = ''
select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '
from master.dbo.sysprocesses
where db_name(dbid) = @databaseName
 and
 DBID <> 0
 and
 spid <> @@spid

exec (@execSql)

PRINT 'END: Killed "' + CAST(@NoKilled AS VARCHAR(4))
+ '" active connections to the "' + @databaseName + '" database'
GO

The free online div table generator allows you to create nice grids for your websites. Please subsribe for a htmlg membership to stop adding promotional messages to the edited documents.

Changing the MS Office 2007 Product Key – 32 and 64-bit

Just installed Office to discover that the Key I used has been used too many times and the activation fails. Follow the either of the two instructions below to allow you to put in an alternative key (if you have one!).

If you are using  a 64-bit operating system then Office detects it and install slightly differently. The registry key(s) on 64-bit are at

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Registration

Mike’s Blog that explains how to do this (http://bloggingabout.net/blogs/mglaser/archive/2006/12/12/Change-Office-2007-Product-Key.aspx)

or

the Microsoft  KB Article ‘How to change the product key for Office XP, for Office 2003, and for the 2007 Office system’ (http://support.microsoft.com/default.aspx/kb/895456)

Digg This

Project Web Access Site Error – “The administrator has taken this farm offline. Project Web Access is not available at this time”

I have recently had this error when finishing my Project Server 2007 installation on an existing SharePoint 2007 server farm.

After creating the Project Web Access site (in my case using a host header), I received the following error message

“The administrator has taken this farm offline.  Project Web Access is not available at this time”

The trick here is to remember to take the farm out of Quiesce mode!

  1. You must go to Central Administration > Operations > Quiesce Farm
  2. click the ‘Reset’ button
  3. Now try your PWA URL in a browser. The page should render after some time.

Once the site is live ‘un-Quiecsed’ the PWA should jumped into life and the two project services (Microsoft Office Project Server Queue Service and Microsoft Office Project Server Events Service)will start automatically

Wildcard Certificates and ISA 2004 Warning

After speding some 10 or so hours  chasing completely the wrong issue with ISA server I thought I might save others from this fate.
 
The long and short is if you wish to use wildcard certificates with ISA Server 2004 you will need to know that using them on your internal networks is not supported. This apparently is by design. However if you do or have, you will not get any simple log stating this, but a the ever present error such as
 
Error Code:500 Internal Server Error. The target principal name is incorrect (-2146893022)

returned to your browser and a faily useless log in ISA itself, simply stating the same as above.
 
As I was trying to do an HTTPS to HTTPS bridging using the same wildcard certificate to an internal Apache server, the Apache server also gave errors in the log that through me in the wrong direction.
 
Errors such as
 
[20/Nov/2006 10:43:00 02040] [error] SSL handshake failed: HTTP spoken on HTTPS port; trying to send HTML error page (OpenSSL library error follows)
[20/Nov/2006 10:43:00 02040] [error] OpenSSL: error:1407609C:SSL routines:SSL23_GET_CLIENT_HELLO:http request [Hint: speaking HTTP to HTTPS port!?]

 
in the ssl_engine_log. When you google this you start to come up with lots of worm associated fixes etc.. The real reason is that ISA server will attempt to connect on HTTP when it fails on HTTPS.
 
The long and short of this is, that you need to use a single host certificate for internal servers or do HTTPS to HTTP bridging. This ‘feature’ is no longer the case in ISA 2006 apparently!

Talking about Extend the power of MSN Search Toolbar with Windows Desktop Search

Go here to download new iFilters for search engine

Quote

Extend the power of MSN Search Toolbar with Windows Desktop Search