In one of my regular customers, they are begging the SQL Server 2012 Migration, at the installation process for a SQL Server 2012 Instance on a Windows Server 2008R2 Cluster, they found an error while adding a node to de SQL Instance.

This error didn’t allow them to go forward to add the node on the SQL Instance, there are a few posible causes to check.

• First, check if there are failed test son the Cluster Validation Report. TO solve this problem execute the Cluster Validation Wizard and ensure there’s no failed test or with  an error state (test with a warning state are not a problem)
• The validation process may fail under various conditions. When this issue occurs, you must perform a manual validation to make sure that the hardware configuration is correct before you try any workaround that is mentioned in this article, http://support.microsoft.com/kb/953748
• There’s another cause not so known, for SQL Server to make the cluster verification, it needs to locate the validation reports from all the nodes of the cluster using the fully qualified domain name (FQDN). SO you need to check that when you do a ping between the servers the IP it’s resolved to the FQDN, if is not the case check the DNS and host file.
• Another cause can be the Windows Firewall blocking the validation, in case it’s active.

In this case we checked the first and second probable causes, but the error still appear.

After that we checked the third probable cause and we observed that the IP was been resolved to the NETBIOS name and not to the FQDN name, so we fix that problem, and we retried the add node process to the SQL Instance, this time was successful.

There was one time when I was delivering a Service using a tool that gathers performance data through the SQL Server Counters on Performance Monitor, I got surprised when I tried to review the data and there were no information. Them I checked the SQL Server counter directly on Performance Monitor and the counters were not on the list. That’s why I decided to write this blog, to know what to do if the SQL Server counters on Performance Monitor disappears.

The right and easiest way to recover the SQL counters is to repair the SQL Server binaries, trough add/remove programs. However this will require downtime on the SQL instance (If you’re using a cluster you could do a switch-over to another node)

Therefore there’s another option, you could recover this counter executing the following steps:

First you need to open a command prompt console with administrative rights.

Go to the directory BINN on the SQL Instance you want to fix (Ex: C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn)

Now to avoid any inconsistency eliminate any leftovers of the SQL Counters using unlodctr MSSQLSERVER, for named instances use the format MSSQL$NombreInstancia

Now to load the counters you should use the following command lodctr perf-MSSQLSERVERsqlctr.ini, if you’re using a named instance you should use this format perf- MSSQL$NombreInstanciasqlctr.ini, you could execute the command twice to check that the counters have been added.

Now you should restart the services Remote Registry and Performance Logs & Alerts, using the followings commands:

net stop "Remote Registry"  &&  net start "Remote Registry"  

net stop "Performance Logs & Alerts"  &&  net start " Performance Logs & Alerts " 

If you are using the previous procedure to recover the SQL counters on a SQL Server Cluster, you should rebuild the clusters on both nodes, when you are rebuilding the counters an Active SQL Instance must be on the node.

If after you have executed the above steps the SQL Counters have not appear, you will need to review the registry hives and keys (it’s not recommended to do any changes at the registry level, doing so could lead to instability on the operating system. If you chose to do these changes you should have a backup of the Operating System and Registry, to recover it if necessary), the following registry keys should exist and have a valid configuration.

[HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\<<service name>>\Performance]

Library
Open
Collect
Close
PerfIniFile

You should also check if there’s a key named Disable Performance Counters inside HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib, if it is configure with the value 1, it should be configure with 0. This should allow you to rebuild the registry counters and help files. 

Now it may or not be necessary to rebuild the SQL and/or Operating System counters, look for the SQL Counters on perfmon, if they aren’t there use the previous steps to rebuild then, with the following command you can rebuild all the operating system counters.

cd %systemroot%\system32

lodctr /R

Another consideration that you should have is when you are executing SQL Server 32 bits on an Windows Server 64 bits environment

I hope this post is helpful, remember there’s a ton of good post on our blog that can help you on many SQL Server topics and how to Troubleshoot some problems. See you on the next post!

“The opinions and views expressed in this blog are those of the author and do not necessarily state or reflect those of Microsoft”

A new feature in SQL Server 2012 is the tabular models in Analysis Services, and after facing some issues when I was preparing a demo for a recent event I decided to finish some tests to compare the performance of Tabular Models vs multidimensional models.

First, what it is Analysis Services Tabular Mode. SQL Server includes not only the relation engine but also the analytical engine. Prior to SQL Server 2012, there was only one kind of analytical database in SQL Server (multidimensional database or cubes), now in SQL Server 2012 we have a new kind of analytical database engine, Analysis Services in tabular mode, the objective is similar to the multidimensional database, be able to answer questions about the data as fast as possible, however, the internal architecture and the language used (DAX instead of MDX) are different. Analysis Services in tabular mode holds all the information in memory in columnar storage (instead of the classic row based storage), this significantly improves query performance without requiring indexes or aggregations. For more details about tabular mode you can read the related ppt that you can found in http://blogs.technet.com/b/sql_pfe_latam/archive/2012/06/27/1-176-simposio-latinoamericano-de-sql-server.aspx        

In these scenarios, the data used was random and I run this test on a 16GB of RAM Laptop (not a high end Server), however it is useful to illustrate that we need to be careful before choosing one modelo r the other doing the appropriate proof of concepts.

Tabular mode has some advantages over multidimensional but it also has disadvantages.

Some of the advantages of tabular models are:

-          They are easier and faster to develop.

-          If you already have PowerPivot models in production is very easy to evolve these models to tabular models.

-          You can use PowerView on tabular models.

Some disadvantages can be seen in the results of my tests. The scenario consists of a 16.4 GB relational database with data compression containing 100 million rows with less than 10 columns. Here is some data about the size and behavior on both tabular models and multidimensional models on this database.

The previous results don’t mean that you shouldn’t use tabular mode, but should evaluate if it is your best option. Depending on the data, the amount of dimensions, the complexity of measures, etc., the results can change.

Here are the print screens of the previous tests.

The relational database size (no data compression)

Tabular Mode Database size

Multidimensional Database Size

Queries response time in Tabular mode

Queries response time in multidimensional mode

“The opinions and views expressed in this blog are those of the author and do not necessarily state or reflect those of Microsoft”

Esta semana mientras trabajaba en Bolivia escuché al personal de seguridad de un cliente discutir sobre las opciones que podían utilizar para auditar las transacciones realizadas a las tablas de una base de datos. Los interrumpí y les pregunté si habían escuchado a cerca de la configuración de auditorías en SQL Server 2008 y me dijeron que no. Así que aproveché el tema para escribir el siguiente artículo.

La primera pregunta que nos surge es: ¿Porque auditamos?

Revisando la literatura de auditoría de seguridad, los autores coinciden en que el tener políticas de seguridad es un factor crítico para mantener los datos seguros. La auditoría también nos ayuda a identificar lo que accedió un intruso en el evento de que un ataque sea exitoso.

En SQL 2005 el enfoque estaba orientado a asegurarse de que el usuario no tuviera privilegio mayores a los que necesitaba y que los cambios fueran realizados por el personal autorizado. La herramienta que se promovía para auditar los cambios a la base de datos era el SQL Profiler ya que podía auditar cambios al esquema, operaciones de insertar, actualizar o eliminar registros; y eventos relacionados con cambios de permisos o la creación de nuevos Logins.

Una de las mejoras que trajo SQL Server 2008 Enterprise es un incremento en la capacidad de auditoría a través del uso del SQL Server Audit. A través de esta funcionalidad se puede rastrear y registrar de forma automática los eventos que ocurren a nivel del servidor o a nivel de la base de datos. Esto es posible a través del uso del objeto Audit. Veamos entonces como crear una auditoría, como crear y habilitar una especificación de auditoría a nivel de Servidor o a nivel de base de datos y como visualizar los registros de auditoría.

Crear una Auditoría

Un objeto de auditoría es una colección de una o más acciones individuales o un grupo de acciones que podrán ser rastreadas. Por ejemplo, se puede configurar un objeto de auditoría para identificar todos los logins fallidos. Los eventos se escriben en la localización que se especifique. Se pueden almacenar en un archivo, la bitácora de eventos de aplicaciones o la bitácora de eventos de seguridad.

El objeto de auditoría se puede crear a través del Management Studio (SSMS) o utilizando T-SQL. Desde SSMS se debe presionar el botón de la derecha del mouse sobre la opción New Audit localizada en la carpeta de auditoría bajo el árbol de Seguridad, como se muestra a continuación:

En la pantalla Create Audit se debe ingresar el nombre del objeto de auditoría, y se debe especificar el destino. Si se indica que el destino será un archivo, hay que especificar la ruta donde será almacenado. Finalmente se debe presionar el botón OK para crear el objeto de auditoría.

Para propósitos de este ejemplo cree un segundo objeto de auditoría e ingrese el nombre “AuditarConsultasTablaEmpleado”. Seleccione como destino un archivo e indique la ruta donde desee almacenarlo. Ambos objetos estarán localizados bajo la carpeta Audits, como se muestra a continuación. Este objeto de auditoría se utilizará para rastrear las transacciones SELECT realizadas contra la tabla HumanResources.Employee de la base de datos AdventureWorks2008R2.

Si desea crear un objeto de auditoría a través de T-SQL lo puede hacer utilizando el comando CREATE SERVER AUDIT. La siguiente consulta crea el objeto  “AuditarConsultasTablaEmpleado”. Esta operación fue realizada anteriormente a través de SSMS.

USE master

CREATE SERVER AUDIT [AuditarConsultasTablaEmpleado]

TO FILE  (FILEPATH = N'C:\TEMP');

Crear y habilitar una especificación de auditoría a nivel de Servidor

Una vez se ha creado los objetos de auditoría, el siguiente paso es crear las especificaciones apropiadas de auditoría. Las especificaciones de auditoría le indican al objeto de auditoría lo que debe rastrear. En el caso del objeto de auditoría llamado “AuditarLoginsFallidos”, debemos crear una especificación que busque los logins que no son exitosos. Para lograrlo debemos seleccionar el botón de la derecha del mouse sobre la carpeta “Server Audit Specifications” ubicada bajo el árbol de Seguridad.

Asigne a la especificación de auditoría el nombre: “EspecificacionAuditoriaServidor-LoginsFallidos”. Bajo audit seleccione la opción “AuditarLoginsFallidos”. Esto asignará la especificación de auditoría “EspecificacionAuditoriaServidor-LoginsFallidos” al objeto de auditoría “AuditarLoginsFallidos”. Seleccione el tipo de acción para auditar: “FAILED_LOGIN_GROUP” y presione OK para crear y asignar el objeto de auditoría.

Presione el botón de la derecha del mouse sobre “EspecificacionAuditoriaServidor-LoginsFallidos” para habilitar la especificación a través de la opción: “Enable Server Audit Specification”

Finalmente debe habilitar el objeto de auditoría presionando el botón de la derecha del mouse sobre “AuditarLoginsFallidos” para habilitar la auditoria a través de la opción Enable Audit como se muestra en la siguiente figura.

Si desea crear una especificación de auditoría a nivel de servidor, a través de T-SQL, lo puede hacer utilizando el comando CREATE SERVER AUDIT SPECITICATION. La siguiente consulta crea la especificación de auditoría  “EspecificacionAuditoriaServidor-LoginsFallidos”. Esta operación fue realizada anteriormente a través de SSMS.

USE master

CREATE SERVER AUDIT SPECIFICATION [EspecificacionAuditoriaServidor-LoginsFallidos]

FOR SERVER AUDIT [AuditarLoginsFallidos]

ADD (FAILED_LOGIN_GROUP) WITH (STATE = ON)

GO

Crear y habilitar una especificación de auditoría a nivel de base de datos

Para crear y habilitar una especificación de auditoría a nivel de base de datos debe expandir la base de datos, en este ejemplo utilice AdventureWorks2008R2, y seleccione con el botón de la derecha del mouse la opción  “Audit Specifications” bajo el árbol de seguridad de la base de datos. Seleccione la opción “New Database Audit Specification” y asigne un nombre (para propósito de este ejemplo asignaremos el nombre: “EspecificacionAuditoriaDB-ConsultasTablaEmpleado”)

Como se muestra en la siguiente figura, seleccione la opción “AuditarConsultasTablaEmpleado” Bajo audit. Esto asignará la especificación de auditoría “EspecificacionAuditoriaDB-ConsultasTablaEmpleado” al objeto de auditoría “AuditarConsultasTablaEmpleado”.

Para propósitos de este ejemplo, seleccione “SELECT” en el tipo de acción para auditar; en la clase del objeto seleccione “OBJECT”; en el nombre del objeto ingrese [HumanResources].[Employee]; en el nombre del Principal ingrese [public] y presione OK para crear y asignar el objeto de auditoría.

Esto permite rastrear las consultas con SELECT que realizan todos los usuarios a la tabla [HumanResources].[Employee].

Presione el botón de la derecha del mouse sobre “EspecificacionAuditoriaDB-ConsultasTablaEmpleado” para habilitar la especificación a través de la opción: “Enable Database Audit Specification”

Finalmente debe habilitar el objeto de auditoría presionando el botón de la derecha del mouse sobre “AuditarConsultasTablaEmpleado” para habilitar la auditoria a través de la opción Enable Audit como se muestra en la siguiente figura.

Si desea crear una especificación de auditoría a nivel de base de datos, a través de T-SQL, lo puede hacer utilizando el comando CREATE DATABASE AUDIT SPECITICATION. La siguiente consulta crea la especificación de auditoría  “EspecificacionAuditoriaDB-ConsultasTablaEmpleado”. Esta operación fue realizada anteriormente a través de SSMS.

USE master

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpec-EmployeesTable]

FOR SERVER AUDIT [Audit-EmployeeQueries]

ADD (SELECT ON OBJECT::[HumanResources].[Employee] BY [public])

WITH (STATE = ON)

GO

Visualizar los registros de auditoría

Los registros de auditoría pueden ser accedidos a través la opción “View Audit Logs” o a través de la bitácora de eventos de aplicaciones o de seguridad, dependiendo de dónde se especificó que se almacenaría la auditoría.

Los resultados pueden ser filtrados o inclusive hasta exportados en los siguientes formatos: log, csv y txt

Conclusión

La auditoría en SQL 2008 Enterprise es muy poderosa y flexible ya que permite crear auditorías a nivel de servidor o a nivel de base de datos. La configuración es sencilla ya que solo se requiere especificar donde se almacenará la auditoría (en un archivo, la bitácora de eventos de aplicaciones o la bitácora de eventos de seguridad), que objeto se desea auditar (Base de datos, Esquema, Tablas, Funciones, Procedimientos Almacenados o Vistas) y para cual Principal  (entidades que pueden solicitar recursos de SQL Server).

After testing many times and configuring FBA in many Virtual environments, finally I think I wrote the easiest and clearest procedure and hope what I'm saying is true, please read carefully and do not skip any step.

To configure FBA using SQL Server with Windows Integrated Authentication (recommended) we’ll follow the following steps.

Pre-requisites        

a)      Knowledge about SharePoint 2010, IIS 7.5 and SQL Server 2008

b)      SQL Server configured as Windows Integrated Authentication

c)       ASP.Net SQL Server Database (Membership database)

d)      Windows Domain user account (Can be SharePoint Farm Administrator)

e)      Make sure you backup web.config files for Central Administration Site, Security Token Service web service and also the Web Application you will use for FBA

Steps to configure FBA

1. Configure SQL Server security settings to use Windows Integrated Authentication
2. Add the Domain User Account to SQL Logins
3. Create SQL FBA database to store non-Windows domain users
4. Grant SQL Login (Domain user) permissions for the new SQL FBA database
5. Configure SharePoint Central Administration
6. Configure  SharePoint Security Token Service
7. Create the new Web Application to use FBA and Site Collection
8. Configure IIS Default providers for the new web application
9. Create IIS Site to administer ASP.NET users

Configure SQL Server to use Windows Integrated Authentication

Basically when you install SQL Server by default Windows Integrated Authentication is already set, in case your DBA team configured SQL Server with Mixed-Mode you have to do the following:

1. Go into the Management Studio of the instance of your SharePoint database server
2. Right click on SQL Server Instance name and choose properties
3. On the left pane go to Security
4. On the right pane choose the option Windows Authentication mode

If you did the change you MUST restart SQL Server service or reboot the server

Add the Domain User Account to SQL Logins

Now we need to add the Domain user account to the SQL Logins in order to provide full access to the ASP.NET membership database we’ll create later.

Two things are important here, you have to decide if you will use the SharePoint Farm Administrator account you are already using or create a new Windows domain user account to manage the access to the ASP.NET membership database. I recommend using SharePoint Farm administrator, whatever you decide here are the steps. (Ask for your DBA team to help you creating the new login)

1. Once you have decided which account to use open SQL Server Management Studio
2. On the left pane expand the SQL Server instance and choose Security folder
3. On Logins right click and choose New Login
4. Look for your Windows domain user account and keep Windows authentication option marked
5. Click OK

Create SQL FBA database to store non-Windows domain users

Open the ASP.Net wizard and creating the database which will store non-Windows domain users for your extranet access:

-          Go to the path: C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe

-          Choose “Configure SQL Server for application services”

-          Type the SQL Server name you want. You don’t have to use the SharePoint database server

-          Keep Windows Authentication as default

-          Use “Default” or type the database name you will create at the end of the process

The first time you create the ASP.NET database you have to choose Windows Authentication and create or USE an existing database*

-          Just confirm the settings and that’s it for this part

 Grant SQL Login (Domain user) permissions for the new SQL FBA database

-          Go back to the SQL Server Management Studio

-          Locate the ASP.NET membership database you already created or restored

-          Right click and choose properties

-          On the right pane click on the square with dots and look for your domain user account (Type domainname\username) and click OK

-          Copy the name from the field Login name to the upper field named User name

-          At the bottom of the window locate Database Role Membership section and mark db_owner and click OK

Configure SharePoint Central Administration

Now we have to create a Connection String, Role Provider, and Membership Provider in IIS Manager to get access to the ASP.NET membership database that we already created.

Let’s do this once for SharePoint Central Administration site and then repeat the steps for the Security Token service web service, the SharePoint Web Application and at the end for the IIS site that we will use to mange ASP .NET Memberships.

-          Open IIS Manager and locate SharePoint Central Administration v4 site under Sites folder

-          On the right pane double click on Connection Strings

-          On the very right pane called Actions click on Add

-          Type a name

-          Type the name of the SQL Server

-          Type the name of the database server you already created

-          Keep selected the option Use Windows Integrated Security

-          Click OK

-          Go back to the IIS to create both providers

-          Double click on Providers in IIS Features view and create Role Provider and Membership Provider

-           On Features drop down menu click .Net Roles first to configure role provider, then click on .Net Users to configure Membership provider

-          This is it for Central Administration site

Configure SharePoint Security Token Service

Repeat the steps just before to configure Security Token service web service. Use same Connection String,  Role Provider and Membership Provider names

Create the new Web Application to use FBA and Site Collection

Open SharePoint Central Administration Site and let’s create the new Web Application that you will use to configure Forms-Based Authentication

Remember in SharePoint 2010 the only way to configure FBA is creating web applications with Authentication option “Claims Based Authentication” otherwise you won’t be able to do it.

-          So open Central Administration web site

-          Go to Application Management category and click on Manage Web Application

-          On the ribbon click on New

-          Choose Claims Based Authentication authentication option in order to enable Forms Based Authentication options

-          It is important for you to keep Enable Windows Authentication with NTLM check box marked for two things:

• Crawl works for that content database
• Active Directory users will use Authentication page to get the content

-          Now check Enable Forms Based Authentication (FBA) and type both Membership Provider and Role Provider

-          Now we have to create a Site Collection, you may use the one for Teams or any other, it’s up to you

-          At this point the Site Collection Administrator MUST be the SharePoint Farm Administrator since our ASP.NET SQL database is empty

-          Create the Connection String, Role Provider, and Membership Provider following the same steps like we did for Central Administration v4 Site

Create IIS Site to administer ASP.NET users

Create an IIS site to manage roles and user account that will live in our ASP.NET membership database

-          Create the IIS Site from IIS Manager

-          Review again the steps we performed for Central Administration v4 site in order to configure:

• Connection String
• Role Provider
• Membership Provider

-          For the IIS Site you should not revert the default provider to “i” or “c”, this time default providers will be FBARoleProvider & FBAMembershipProvider

-          Test time, log in to the new SharePoint site configured with FBA

-          First use Windows Integrated Authentication, successful?

-          Now choose sign in with a different user, successful? I’m sure not since you have not granted permissions for FBA users yet

-          Go back to IIS Manager and create FBA roles and users

-          Ok, now we can add FBA users to SharePoint sites, login back with SharePoint Farm Administrator

-          Open Site Actions > Site Permissions

-          Pick a group you want to add the new FBA user

-          Click New

-          Click the address book or type the name of the FBA user

I wish you good luck and remember that feedback is very important for us, please let me know if this post is really useful and if it was easy to configure FBA in SharePoint 2010 as I wrote