Configuring SQL Server 2008 R2 Express Edition for remote access

Often I see questions in forums about how to configure SQL Server Express Edition for remote access.

 

Below are the steps to allow remote access to a SQL Server 2008 R2 x64 Express Edition instance after the default install steps where performed.

 

Also included are the additional configuration steps for SQL Server 2008 R2 x64 Express Edition with Advanced Services.

 

The steps were performed on  Windows Server 2008 R2 64bit.

 

 

 

 

 

Below you can see that by default, SQL Server Express allocates a Dynamic port when SQL Server starts.

 

You can either keep this setting or change SQL Server to listen on a fixed TCP port e.g. TCP 1433.  This can be achieved by removing 0 from all the ‘TCP Dynamic Ports’ rows and placing the fixed TCP port you want to use on all the ‘TCP Port’ rows below it.

 

For this demo I will keep SQL Server allocating a Dynamic port on start-up and therefore I have not changed anything on this tab.

 

 

 

 

 

Connect to SQL Server Express on the server you installed it. 

 

As below you can see the instance when SQL Server Express is installed is call SQLEXPRESS if you did not change it.

 

 

The below should already be enabled, but if it isn’t, then enable it and restart the SQL Server service.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The Port rule  I am creating below is for if you installed SQL Server Express with Advanced Services and selected the SQL Server Reporting Services feature.

 

 

 

 

 

 

If you changed the HTTP TCP port from the default on 80, then you should enter the port you are using here.

 

 

 

 

 

 

 

 

 These steps show you how to give a user access to Reporting Services.

 

 

 

As below, make sure you use localhost and not the name of the SQL Server.

 

 

 

 

 

 

 

Add the relevent windows user with the permissions they require.

 

 

 

 

Now test that you are able to connect from a remote computer.

 

 

 

 

 

Below shows a remote connection to Reporting Services.  Above I used localhost when on the SQL Server, but remotely you should use the SQL Server name.

 

 

Resolve log_reuse_wait_desc is REPLICATION

Occasionally I come across instances with  the following symptoms:

 

  • The log file is unable to be shrunk
  • The log file log_reuse_wait_desc is REPLICATION
  • There is no replication setup on the SQL Server instance

 

The below is an example:

 

 

These are the steps you can take to resolve this:

 

 

 

Select the database with the issue

 

 

The Snapshot publication is a good option as schema changes are not made

 

 

Selecting any sproc is an easy option

 

 

You will notice the below objects have now been created and the log_reuse_wait_desc is still REPLICATION

 

 

 

 

The replication created objects are now gone and the REPLICATION message is gone.

 

Query and update an Excel 2010 spreadsheet via a linked server

This took a while to get working so I thought I’d list the steps taken to achieve manipulating an Excel 2010 spreadsheet via a linked server.

 

The SQL Server I used was SQL Server 2008 R2 (64-bit) and the Excel 2010 spreadsheet had the below data:

 

 

First  I downloaded and installed the ‘AccessDatabaseEngine_x64.exe’ file on the SQL Server from http://www.microsoft.com/download/en/details.aspx?id=13255. No restart was required.

 

Next I started SQL Server Management Studio as ‘Administrator’.

 

 

Next I enabled the ‘Allow inprocess’ option for the Microsoft.ACE.OLEDB.12.0 provider.

 

 

The linked server was created with this script:

 

EXEC master.dbo.sp_addlinkedserver
 @server = N'test',
 @srvproduct=N'ACE 12.0',
 @provider=N'Microsoft.ACE.OLEDB.12.0',
 @datasrc=N'c:\test\test.xlsx',
 @provstr=N'Excel 12.0;HDR=Yes'

 

 

I could now query the spreadsheet with:

 

select * from test...[sheet1$]

 

 

To add data I used:

 

insert into test...[sheet1$] values ('server2','blue')

 

To modify data I found that I had to first enable the below:

 

 

Then to update a record I used

 

UPDATE OPENROWSET('Microsoft.Ace.OLEDB.12.0',
  'Excel 12.0;DATABASE=c:\test\test.xlsx', 'Select * from [Sheet1$]')
  SET type = 'green' WHERE server = 'server2'

 

 

To insert a row I used:

 

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',  
 'Excel 12.0;Database=C:\test\test.xlsx;',
 'SELECT server,type FROM [Sheet1$]')
 VALUES ('server3','yellow')

 

 

To delete 1 row I used the below work around which set the column values to null.

 

UPDATE OPENROWSET('Microsoft.Ace.OLEDB.12.0',
 'Excel 12.0;DATABASE=c:\test\test.xlsx',
 'SELECT server,type from [Sheet1$]')
 SET server = null, type = null
 WHERE server like 'server2'

 

 

SQL Server 2008 R2 server collation change issue

When you attempt to change a SQL Server 2008 R2 server collation using the instructions in Setting and Changing the Server Collation (shown below), this may fail with an invocation error.

 

Setup /QUIET /ACTION=REBUILDDATABASE

/INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts

/[ SAPWD= StrongPassword ] /SQLCOLLATION=CollationName

 

You could try resolving this issue by running the command using setup.exe from the installation media.