Category: SQL Server

Azure SQL with AAD authentication

I though this had to be an easy task. Well, actually it is. If you find the right documentation and read it in the correct order 🙂

Basically I wanted to be able to login with my AAD (Azure Active Directory) user.

In the first step, the database needs to be configured for Azure Active Directory in order to add users in the second step.

Configure an Administrator

In the Azure portal go the the SQL server and search for “active directory” to add an Active Directory admin.

After you’ve added an admin and saved the value, you will be able to use SSMS (SQL Server Management Studio) to logon to the server. Probably SSMS will prompt you about a firewall exception.

Use SQL Management Studio to add users and grant permissions

For other users (not the administrator we configured above) to be able to logon, access has to be granted like with an on premises SQL Server.

Add a user to the master DB

Create a new query o

Next grant permissions to the user on the database itself.

Add user to database

Open another query on the database.

That should be it.

Some documentation I used:

Reporting Services Exception

If your Report Server 2008 instance is configured as SharePoint integrated and you get an exception like this one, keep on reading.

image

Additionally to the exception in the browser, the Eventlog revealed two more exception which are relevant to the SSRS exception. One was about the SSRS process, which quit unexpected, the other one from ASP.NET.

  1. “The SQL Server Reporting Services (MSSQLSERVER) service terminated unexpectedly.  It has done this 6 time(s).  The following corrective action will be taken in 60000 milliseconds: Restart the service.” Event ID 7031
  2. “.NET Runtime version 2.0.50727.4952 – Fatal Execution Engine Error (000007FEF523FA42) (80131506)”, Event ID 1023

My solution was to log on once to the SharePoint server as the account, which is used by the Reporting Services (remember: we’ve configured SSRS in SharePoint integrated mode).

RSS feed for Knowledgebase Articles

Shrink SQL log files

Depending on the configuration of you databases, the log files (*.ldf) can be very large. See Selecting a Recovery Model.

Should I continue reading?

If your databases are configured to use the FULL recovery model, the log files are very large and you need some free space, you can use this SQL script to shrink them. So keep on reading 🙂

The script will change the recovery model for a single database to SIMPLE and shrink the log files. At the end it will restore the recovery model to the setting it has been before.

Just copy the script to a new Query window in your SQL Management Studio. Replace the @databaseName and run the script by hitting F5. You can also change the size in MB the log file should be shrunken to.

USE [master]
GO
DECLARE @debug int
DECLARE @databaseName nvarchar(255)
DECLARE @logfile nvarchar(255)
DECLARE @newFileSize nvarchar(5) -- in MB
DECLARE @sql nvarchar(4000)
DECLARE @parmDefinition nvarchar(500)
DECLARE @recoveryModel nvarchar(10)

--
-- CHANGE HERE BEGIN
set @debug = 1
set @newFileSize = 100 -- in MB
set @databaseName = 'your database name'
-- CHANGE HERE END
--

IF @debug = 1 PRINT 'DatabaseName=' + @databaseName

-- get recovery model from database
SET @sql = N'SELECT @model = recovery_model_desc FROM sys.databases WHERE [name]=''' + @databaseName +''''
SET @ParmDefinition = N'@model nvarchar(255) OUTPUT';
EXECUTE sp_executesql @sql, @ParmDefinition, @model = @recoveryModel OUTPUT;
if @debug = 1 PRINT 'Recovery model=' + @recoveryModel

-- set recovery modell to simple
SELECT @sql = 'ALTER DATABASE [' + @databaseName + '] SET RECOVERY SIMPLE'
IF @debug = 1 PRINT @sql
EXECUTE sp_executesql @sql

-- shrink log file
-- get logfile from database
SET @sql = N'USE [' + @databaseName + ']; SELECT @logfileName = [name] FROM sys.database_files WHERE [type_desc]=''LOG'''
SET @parmDefinition = N'@logfileName nvarchar(255) OUTPUT';
EXECUTE sp_executesql @sql, @parmDefinition, @logfileName = @logFile OUTPUT;
if @debug = 1 PRINT 'Logfile Name=' + @logfile

SELECT @sql = 'USE [' + @databaseName + ']; DBCC SHRINKFILE ([' + @logfile + '] , ' + @newFileSize + ')'
IF @debug = 1 PRINT @sql
EXECUTE sp_executesql @sql

-- restore recovery modell
SELECT @sql = 'ALTER DATABASE [' + @databaseName + '] SET RECOVERY ' + @recoveryModel + ''
IF @debug = 1 PRINT @sql
EXECUTE sp_executesql @sql

-- done

Service Pack 1 for Microsoft SQL Server 2008

The SP1 has been released. You can download it here. (Release Notes)

Some changes to make the deployment easier:

  • Slipstream – You are now able to integrate the base installation with service packs (or Hotfixes) and install in a single step.
  • Service Pack Uninstall – You are now able to uninstall only the Service Pack (without removing the whole instance)
  • Report Builder 2.0 Click Once capability

SharePoint and Database Maintenance

In this post I would like to point to “an issue” with the Database Maintenance for Microsoft® SharePoint® Products and Technologies. It shows a stored procedure, which will defragment the index of a database.

How to defragment Windows SharePoint Services 3.0 databases and SharePoint Server 2007 databases

The problem is that it is mentioned nowhere, that the stored procedure requires the Enterprise version of the SQL Server 2005!

Configuring SQL Server 2008 Reporting Services

Reporting Services Configuration Wizard

The SQL Server 2008 Reporting Services do not need an Internet Information Server anymore. You can host your Reports website from within Reporting Services only. If you have installed the IIS, you can still use Reporting Services, because it will register itself via HTTP.sys.

Because we configured the service account during the installation process, it is already configured.

Quote from the SQL Server 2008 Online Books:

Before you can use Report Manager or the Report Server Web service, you must configure at least one URL for each application. Configuring the URLs is mandatory if you installed Reporting Services in "files-only" mode (that is, by selecting the Install but do not configure the server option on the Report Server Installation Options page in the Installation Wizard). If you installed Reporting Services in the default configuration, URLs are already configured for each application. If you have a report server that is configured to use SharePoint Integrated mode and you update the Report Server Web Service URL by using the Reporting Services Configuration tool, you must also update the URL in SharePoint Central Administration.

Use the Reporting Services Configuration tool to configure the URLs. All parts of the URL are defined in this tool. Unlike earlier releases, Internet Information Services (IIS) Web sites no longer provide access to Reporting Services applications in SQL Server 2008.

If you want to run your Reporting Services Instance with a host header, you specify the IP where the host header will be resolved to. In my case the host header will be resolved to this IP by the DNS Server. You do not have to create an IIS Website with a host header anymore. All is done via DNS.

Next the wizard will create the necessary databases.

For a new instance, we create a new database. So click on the "Change Database" Button. A wizard will guide you through the creation process.

The Report Manager URL will adjust to the Webservice URL configured previously.

To be able to send emails, you have to configure the email settings.

Remote data sources which do not require credentials will be access with the execution account.

Before you leave the configuration wizard backup your encryption key! In case of a restore you will need this key if you attach the database to a new instance.

As you can see, the Reporting Services can be called through host header.

Tags:

Install SQL Server 2008

Installing the new SQL Server is not very different from installing older version. You have some new options, don’t have to change the media anymore and have some nice wizards.

The SQL Server 2008 requires some components, which will be installed automatically during the setup process.

  • .NET Framework 3.5 SP1 (installed before SQL Server is being installed)
  • Hotfix for Windows Server 2003 (KB942288-v4 will be installed)
  • Powershell 1.0 (will be installed later)

After installing the Hotfix, the System requires a reboot! The setup does not continue automatically after the restart. So you have to start it again.

The installation wizard calls itself "SQL Server Installation Center". You will start at the planning page. "System Configuration Checker" will inspect your Server, and show missing requirements, or that everything is OK.

  

To start the installation, change to the "Installation" page and select the first option to install a new SQL Server instance.

A small test will inspect your system, to make sure all requirements are met.

Select the edition you want to install, or enter your license key (if it is not entered by the setup).

The "Setup Support Files" will be installed, if they are missing. This will be the case if you initially install your SQL Server.

After all the required steps are done, we can finally select which components we want to install.

From here, MS changed the working dialog to

If you are installing the "Database Engine Services", you will be prompted to enter an instance name. You will notice that you can change the directory where this instance will be installed. This is great, because we don’t have to change database default paths anymore.

The summary will show you how much data will be copied to your hard disk:

Installation options – Database Engine

Service accounts

Configure the service accounts. And please do not use the same account for every service. Creating additional accounts for the services does not take a lot of time, but is safer and cleaner! (Btw: each service account has its own password!)

Authentication mode

You will need to specify at least on SQL Server administrator. If you want, you can still activate Mixed Mode. If you do, SQL Logins will be possible.

Data Directories

When we created the SQL Server Instance earlier, we changed the data path. We can fine tune the paths now.

Filestream

With the Filestream option, you can configure a directory where blob data will be stored. This way binaries will not be stored in the database. For more information http://msdn.microsoft.com/en-us/library/bb933993(SQL.100).aspx

Installation options – Reporting Services

Let’s just install the SSRS. Configuration will be done later.

Error and Usage Reporting

If you want, you can enable error reporting.

Installation Summary

A configuration file will be written to your hard disk:

Installation

Now you can get yourself a cup of coffee… hmm. Maybe another one…

When the Installation is completed, we can configure additional settings.

SQL Server Installation Center

The Install Wizard gives you some additional options. The "Edition Upgrade" option is one of the new features that come along with SQL Server 2008.

Remember when we got the path for the ini file which stores installation options? We can use this file to install more servers with the same options via "Install based on configuration file". Meaning you can configure options through the wizard, let it create the ini file, cancel the setup and use the ini file for further installations. Now that is a cool feature!

Here are some options which you would need before you install your server. Well, at least you have the option to configure it, because all installation bits are on the same DVD.

Tags:

Setting up Reporting Services with not default database name

Setting up the Reporting Services with a non default database name requires that you create a Script, and change the database names in this script, because otherwise the creation of the databases fails.

Before you can generate the Script, start up the Reporting Services Service in the first Dialog of the Wizard!

In the "Database Setup" Dialog of the Reporting Services Configuration Wizard hit the "Script" Button. (My Database Name is "ReportServer2005).

After the Script has been generated, open it with SQL Server Management Studio (or you favorit Editor), and replace [ReportServerTempDB] with your Database name ([ReportServer2005TempDB]). In my case this was in the lines 14036 and 14047.

INSERT INTO [ReportServerTempdb].[dbo].[ChunkData] (
needs to be changed to
INSERT INTO [ReportServer2005Tempdb].[dbo].[ChunkData] (

and

FROM [ReportServerTempdb].[dbo].[ChunkData]

to

FROM [ReportServer2005Tempdb].[dbo].[ChunkData]

After that, you can execute the script. It will generate the databases, and set them up for usage with Reporting Services.

HowTo use the resource files from SharePoint

How about using the available resources from SharePoint to translate some basic words and sentences? Well, it is quite easy to use the available resources. You need the Microsoft.SharePoint.Intl.dll and some lines of code to use the already translated resources:

But how do you know how the resource name for your translated text might be? Your friend and helper is the Reflector. Open up the ‘Microsoft.SharePoint.Intl.dll’ from the ‘C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SharePoint.intl\12.0.0.0__71e9bce111e9429__71e9bce111e9429c’ folder (navigate to that path with cmd.exe and copy the dll to some safe place like c:\temp).

A big thanks goes to Janne Mattila for the information in the comment for this blog entry.

Custom assemblies for the Reporting Services 2005

Even if the Reporting Services are very powerfull, you might get to the point where you have to extend the building functionality. So why not write your own custom assembly with some additional code?

Create your assembly like this:

   1:  using System;
2: using System.Security.Permissions;
3: using Microsoft.SharePoint;
4:  
5: public class MyNamespace
6: {
7: public class ReportingExtension
8: {
9: public ReportingExtension()
10: {
11: }
12:  
13: public static string HelloWorld()
14: {
15: return "Hello World.";
16: }
17:  
18: [EnvironmentPermission(SecurityAction.Assert, Unrestricted = true)]
19: [Microsoft.SharePoint.Security.SharePointPermission(SecurityAction.Assert, Unrestricted = true)]
20: public static string getSomeListItem(string url, int itemID)
21: {
22: string returnvalue = string.Empty;
23: try
24: {
25: using (SPSite site = new SPSite(url))
26: using (SPWeb web = site.OpenWeb())
27: {
28: // do something
29: returnvalue = "";
30: }
31: }
32: catch (Exception ex)
33: {
34: return ex.ToString();
35: }
36:  
37: return returnvalue;
38: }
39: }
40: }

 

Put this line into your AssemblyInfo.cs:
[assembly: AllowPartiallyTrustedCallers]

and sign your assembly with a strong name (see here for details).

Next you have to reference your assembly in your Reporting project in the Report Properties:

You can then use your code like this:

=MyNamespace.ReportingExtension.getSomeListItem("http://site/web", 5)

Before you can use your custom assembly, you have to deploy it to the following paths:

  • Report Designer C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer
  • Reporting Server C:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\ReportServer\bin

And register it in the rssrvpolicy.config file:

<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="MyNamespace.ReportingExtension"
Description="Code for SSRS">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="c:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting
Services\ReportServer\bin\MyNamespace.ReportingExtension.dll"
/>

 

Now you are good to go J

Reporting Services and XML Datasource

Querying a XML Datasource

Query:

<Query>

    <SoapAction>http://ns.tld/MethodName</SoapAction>

    <Method
Namespace="http://ns.tld/"
Name="

MethodName" />

    <ElementPath
IgnoreNamespaces="True">*</ElementPath>

</Query>

Passing Parameters

You only have to define your parameters in the parameter tab of your dataset. They are passed automatically to the Webservice Method. If you want to define your parameter yourself and not use a Reporting Services Parameter, just type

<Query>

    <SoapAction>http://ns.tld/MethodName</SoapAction>

    <Method
Namespace="http://ns.tld/"
Name=" MethodName">

        <Parameter
Name="ParamName">

            <DefaultValue>ParamValue</DefaultValue>

        </Parameter>

    </Method>

    <ElementPath
IgnoreNamespaces="True">*</ElementPath>

</Query>

Reporting on SharePoint Lists

You can do Reporting on SharePoint Lists with SQL Server Reporting Services 2005 quite easy.

  1. Configure the Datasource with integrated Security
  2. Define a query like

    <Query>
    <SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
    <Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">
    <Parameters>
    <Parameter Name="listName">
    <DefaultValue>SharePoint List Name</DefaultValue>
    </Parameter>
    </Parameters>
    </Method>
    <ElementPath IgnoreNamespaces="True">*</ElementPath>
    </Query>

  3. Hit the "Field Refresh" Button to get the Field from your SharePoint List
  4. Drag your Fields in the Report
  5. Use the Code to eliminate "ID;#" and "<div>","</div>" from your Fields

    Use "=Code.CuttOff(Fields!ows_LinkTitle.Value)" in your Report to display your Fields