Friday, November 30, 2007

Concerning the credentials double hop issue

Este articulo tomado de http://blogs.msdn.com/nunos/archive/2004/03/12/88468.aspx muestra posibles soluciones a el problema de paso credenciales a traves de multiples servidores

Being an ASP.NET enthusiast, I've soon came across this issue and had to deal with it. This happened very shortly after the release of the .NET Framework and, at the time, seemed like a major headache for developers. Well, actually it still does seem like a major headache but I've came to accept it as a "normal" thing :P.

Because I regularly post in ASP.NET newsgroups, I realized that every so often someone sends in an email stating that they lose the user credentials connecting to a SQL Server or accessing a file in a network share from within their ASP.NET application. And this is called the "double hop" issue.

The typical scenario follows: you're building an intranet application and using Integrated Security. You're also setting "" in your web.config file. During an ASP.NET request you can identify the current user but when accessing SQL Server in a trusted connection you get a logon failure stating that user Anonymous isn't allowed. So, basically, it seems that the credentials get lost somewhere in the middle.

Let's look at it step-by-step:

Integrated Security - What this means is that IIS doesn't explicitly ask the users for credentials. Instead the credentials used to log on to their workstation are used to authenticate against IIS. And here is the first hop: the user's credentials are passed from the workstation to the web server in a secure way.

Impersonate - This is a very cool feature. You can access resources in the context of the user currently using your application. This simplifies resource access control but it's only suited for resources hosted on the web server itself.

Double hop - For security reasons, NTLM credentials cannot hop between servers at will. So, accessing a SQL Server instance running on another machine other than the web server will result in a logon failure error. It would be the second hop: the web server would have to pass the user's credentials to the SQL Server machine. For security reasons this is not allowed. Imagine that you access a site through your browser, the site collects your credentials without your knowledge (Integrated security) and then starts doing whatever the developer desires with your identification. Clearly, this is not a good scenario.

Ok, and now that we identified the problem, how do we workaround it?

There are a lot of possible ways to workaround this issue, but I actually couldn't find one that completely satisfies me. Oh well, security is a process and one that should be between our major concerns when writing an application. Here you'll find possible solutions to this problem. Note that you should carefully analyze each of these options and choose whichever fits your security and design requirements.

Basic Authentication (consider HTTPS in this case)
Kerberos and Trust computer for delegation privilege in AD
Specify explicit credentials
1. When using basic authentication, users are asked to insert their credentials when accessing the site. In this case, they are authenticating against the web server and the machine can now use the credentials provided to access SQL Server. You can think of this as the user giving permission to the machine to use his/her identification. This doesn't change anything in your code, it's only an IIS setting. Careful though, because the credentials are sent in clear text between the client machine and the web browser, so you should think of getting a SSL certificate and using https to secure communications.

2. It's a lot more complex to handle authentication this way but Kerberos would allow you to hop the user's identification between servers. You'll also have to give the web server the "Trust machine for delegation" privilege in AD. You'll need to ask a domain admin to do this and normally they don't really like to accept this type of request. It's not a question of bad attitude but concerns for security. If your domain admin refuses your request feel happy that he knows what he's doing ;) Take a look here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;810572

3. This is the solution more commonly used. Basically, you do not connect to SQL Server using a trusted connection, you specify a username and a password in the connection string or you specify this values in the impersonation settings and still use a trusted connection. The connection is still secure, and the only problem you have is where to store the these values so that it'll be safe. Of course, you could hard-code it, but that would leave you with no easy way to change it afterwards. One way is to store it in web.config and that would give you an easy way to change the parameters in runtime. Although the web.config file is not served by IIS (meaning that a client cannot view it in the browser) it's not the safest way to store this valuable piece of information. The recommendation is storing this values in the registry. This article although not specifically written for this issue, does show a way to accomplish this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;329290

That's about it. This is not meant to be a complete article on ASP.NET security. It justs aims to give a few pointers on working around this common problem.

Hope it helps.

Saturday, November 17, 2007

Resumen de Caracteristicas de SQL Server 2008

Este es un resumen de caracteristicas de SQL 2008 creada por Don Schlichting



Transparent Data Encryption. The ability to encrypt an entire database.

Backup Encryption. Executed at backup time to prevent tampering.

External Key Management. Storing Keys separate from the data.

Auditing. Monitoring of data access.

Data Compression. Fact Table size reduction and improved performance.

Resource Governor. Restrict users or groups from consuming high levels or resources.

Hot Plug CPU. Add CPUs on the fly.

Performance Studio. Collection of performance monitoring tools.

Installation improvements. Disk images and service pack uninstall options.

Dynamic Development. New ADO and Visual Studio options as well as Dot Net 3.

Entity Data Services. Line Of Business (LOB) framework and Entity Query Language (eSQL)

LINQ. Development query language for access multiple types of data such as SQL and XML.

Data Synchronizing. Development of frequently disconnected applications.

Large UDT. No size restriction on UDT.

Dates and Times. New data types: Date, Time, Date Time Offset.

File Stream. New data type VarBinary(Max) FileStream for managing binary data.

Table Value Parameters. The ability to pass an entire table to a stored procedure.

Spatial Data. Data type for storing Latitude, Longitude, and GPS entries.

Full Text Search. Native Indexes, thesaurus as metadata, and backup ability.

Reporting Server. Improved memory management.

SQL Server Integration Service. Improved multiprocessor support and faster lookups.

MERGE. TSQL command combining Insert, Update, and Delete.

SQL Server Analysis Server. Stack improvements, faster block computations.

SQL Server Reporting Server. Improved memory management and better rendering.

Microsoft Office 2007. Use OFFICE as an SSRS template. SSRS to WORD.

SQL 200 Support Ends. Mainstream Support for SQL 2000 is coming to an end. Feature and benefits included in Mainstream Support include the ability to submit requests for product feature changes, Security Updates, Non Security Hotfixes, Complimentary support, and Paid Support. This Mainstream Support will expire on 4/8/2008 for SQL Server 2000 64-bit Edition, SQL Server 2000 Developer, SQL Server 2000 Enterprise, SQL Server 2000 Standard, SQL Server 2000 CE, and SQL Server 2000 Workgroup Edition. Extended Support, consisting of Security Updates and Paid Support will continue until 2013. A full description of support phases can be found at these Microsoft URLs: “Microsoft Support Lifecycle” and “Microsoft Support Lifecycle Policy FAQ”. Many resellers will discontinue selling SQL 2000 in December of 2007. Also, no version of SQL 2000 will be supported on Vista, including SQL 2000 Express.

http://www.databasejournal.com/features/mssql/article.php/3702381

Integration Services en SQL 2008

Dentro de los cambios en SQL Server 2008 se encuentran los siguientes: Integration Services ha sido mejorado para que pueda escalar a mas de dos procesadores, ademas existe un nuevo pipeline redisenado para obtener mayor rendimiento cuando se estan ejecutando paquetes grandes que contienen subarboles, ahora el motor de integration es mas estable y provoca menos deadlocks.

Monday, November 12, 2007

Mejoras en SQL 2008

Con la llegada de SQL Server 2008, algunos de los nuevos tipos de datos que ya son (o serán) novedades tenemos a los siguientes: HIERARCHY ID (para almacenar valores de nodos en un jerarquía), FILESTREAM (para almacenar data de tipo grande, documentos, imágenes), tipos para almacenar datos espaciales como GEOGRAPHY, GEOMETRY, así como los tipos de datos TIME (3 a 5 bytes de tamaño) y DATE (3bytes), DATETIME2 (6 a 8 bytes), DATETIMEOFFSET (8 a 10 bytes), entre otros más.

En este post vamos a hablar acerca de los tipos de datos para manejar fechas y tiempo, es decir, los 4 últimos arriba mencionados. Los otros tipos de datos serán motivos de otro post!.

DATETIME es un tipo de dato compuesto que almacena fecha y tiempo. La novedad es que en SQL Server 2008, el tipo de dato DATETIME se ha separado en dos: DATE y TIME, con esto no quiero decir que DATETIME se haya eliminado, este sigue siendo necesario para otros escenarios, pocos pero en fin. Esta noticia es muy buena ya que por ejemplo, siempre, casi siempre... sólo necesitabamos almacenar la fecha, más no el tiempo, y el único tipo de dato que podiamos usar era DATETIME, el cual "amablemente" guardaba algo más: el tiempo, aunque no lo necesitemos, pues no habia solución. DATETIME aparte de ser un tipo de dato cuyo tamaño es 8 bytes (realmente bastante), es pesado pudiendo originarse problemas de performance general.

Particularmente casi nunca uso DATETIME, prefieron usar con mucho criterio SMALLDATETIME, Porqué?, pues su tamaño es 4 bytes, y para lo que necesito está bien, puedo manejar fechas entre el rango de 1900 y 1979. Obviamente, SMALLDATETIME también almacena la hora, cosa que no se necesita en la mayoria de aplicaciones. De manera semejante sucedía cuando queriamos trabajar sólo con la hora, no había forma de separar la hora de la fecha. Ejemplo:

-- creando un tipo de datos DATE, y asignándole un valor DECLARE @FechaTiempo DATETIMESET @FechaTiempo= GETDATE()SELECT @FechaTiempo AS FechaTiempo
Resultado:FechaTiempo
-----------------------
2007-11-03 16:25:53.653

(1 row(s) affected)

Con los tipos de datos DATE y TIME, este problema se soluciona. DATE para almacenar sólo fechas, y TIME para tiempo.

Para asignar valor a un tipo de dato DATETIME usábamos la función del sistema GETDATE(). GETDATE() devuelve un valor de tipo DATETIME. ¿Y cómo asignamos valor a estos nuevos tipos de datos DATE y TIME?, curiosamente la solución es usar GETDATE(). Asi es, pues fíjate que internamente el Database Engine de SQL Server 2008 al detectar que el valor devuelto por GETDATE() será asignado a un tipo DATE o TIME, este filtra sólo la fecha o sólo el tiempo. Veamos un ejemplo:

-- creando un tipo de datos TIEMPO, y asignándole un valor DECLARE @Tiempo TIMESET @Tiempo= GETDATE()SELECT @Tiempo AS Tiempo -- creando un tipo de datos DATE, y asignándole un valor DECLARE @Fecha DATESET @Fecha= GETDATE()SELECT @Fecha AS Fecha
Resultados:Tiempo
----------------
16:26:22.2800000

(1 row(s) affected)

Fecha
----------
2007-11-03

(1 row(s) affected)

Otro de los tipos para manejar fecha y tiempo son DATETIME2 y DATETIMEOFFSET, a los cuales yo me refiero como extensiones del tipo DATETIME. DATETIME2 es un DATETIME con más precisión y un mayor rangos de años, y con DATETIMEOFFSET se tendrá la posibilidad de manejar fecha y tiempo relacionados a una zona horaria en particular. Cool! :D.

-- creando un tipo de datos DATETIME2, y asignándole un valor DECLARE @FechaTiempo2 DATETIME2SET @FechaTiempo2= GETDATE()SELECT @FechaTiempo2 AS FechaTiempo2 -- creando un tipo de datos DATETIMEOFFSET, y asignándole un valor DECLARE @FechaTiempoOFFSET DATETIMEOFFSETSET @FechaTiempoOFFSET= GETDATE()SELECT @FechaTiempoOFFSET AS FechaTiempoOFFSET
Resultados:

FechaTiempo2
---------------------------
2007-11-03 16:26:47.8270000

(1 row(s) affected)

FechaTiempoOFFSET
----------------------------------
2007-11-03 16:26:47.8270000 +00:00

(1 row(s) affected)

Finalmente puedo decir que la gente de SQL Server creo que está escuchando mis oraciones, hace tiempo que venía necesitando estos tipos de datos, rogando que existan tipos de datos separados para manejar fecha y tiempo.

Tomado de:

http://geeks.ms/blogs/ozonicco/archive/2007/11/04/sql-server-2008-date-time-datetime2-datetimeoffset-a-la-vista.aspx

Que hay de nuevo en SQL 2008

En este articulo encontraran los aspectos nuevos incluidos en SQL Server 2008

Your Data, Any Place, Any Time
SQL Server 2008, the next release of Microsoft SQL Server, will provide a comprehensive data platform that is more secure, reliable, manageable and scalable for your mission critical applications, while enabling developers to create new applications that can store and consume any type of data on any device, and enabling all your users to make informed decisions with relevant insights.

http://www.microsoft.com/sql/2008/prodinfo/newfeatures.mspx
http://www.sqlskills.com/blogs/stacia/2007/10/29/SQLServer2008AttributeRelationshipDesigner.aspx

Thursday, November 01, 2007

Conectividad de Analysis Services

1. Permisos de:
C:\Program Files\Microsoft SQL Server\90\Shared
C:\Program Files\Microsoft SQL Server\90\Shared\ASConfig

2. Reg Key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\SQL Browser
El key AnalysisServiceListener valor de 1

3. Borrar archivos msmdredir.ini y .bak
C:\Program Files\Microsoft SQL Server\90\Shared\ASConfig

Reinicie SQLBrowser y la instancia nombrada.

SQL Server 2016 Stretch Databases

El SQL Server Stretch Database permite que los datos históricos o de bitácoras puedan ser movidos hacia Azure, específicamente permite mover...