Reporting Services in SQL Server 2005 will introduce a new ad-hoc reporting control – the Report Builder. You can view a demo of the new features in a Microsoft On-Demand Webcast: End-User, Ad Hoc Reporting in SQL Server 2005 Reporting Services with Brian Welcker and Carolyn Chau as the presenters.
The Report Builder is not available in the current beta release (beta 2) of 2005, but should be available for beta 3.
Some interesting features include:
- The Report Builder will give end users a way to edit and create reports in Reporting Services. You can restrict who can use the Report Builder, in fact, the feature is disabled by default.
- The Report Builder Client does not have a crappy web interface, it uses ClickOnce deployment to put a rich .NET WinForms application on the client!
- The Report Builder uses semantic metadata to present a “report model“ of the data source to the end user. The end user does not need to know SQL or MDX to build reports.
- SSRS can generate the report model, you’ll also be able to use the Model Designer to edit and augment the model (this will be a new project type in VS.NET). It looks as if the model designer and auto-generation is only available for MS SQL Server and MS Analysis Services.
- The report model also allows tools to automatically generate dill-through reports. If there is a link to describe another layer of data, the user can drill.
- There is still a Report Designer in Visual Studio.NET, which is more powerful than the Report Builder. The Report Builder is for end users, the Report Designer is for report developers. You can import a Report Builder report into the Report Designer to add lower level features, but not vice versa.
- Licensing: there is no additional license to purchase for Report Builder, it is part of the SQL Server 2005 license. For per-CPU licensing, this means unlimited users.
In February I’ll be giving a presentation at VSLive! in San Francisco. The talk will focus on how to coax SQL Server Reporting Services to do your application’s bidding with URL Access and Web Services. No report designing in this presentation, just code, code, and then a bit more code.
I’m looking forward to the conference. In addition to the usual VBITS, C#, ASP.NET, and SQL tracks there are two co-located events: the Software Architecture Summit and the Microsoft Windows Anywhere track for Tablet PC and mobile PC development. Who’s gonna be there?
sqlConnection.Close(); sqlConnection.Dispose(); sqlConnection = null;
There has been plenty of debate about the confusion resulting from aliasing the Dispose method. There has also been heaps of information to explain finalization, garbage collection, connection pooling, and IDisposable. Still, none of the debate or information addresses the fundamental problem in this scenario.
For some developers, the instantiation of a disposable object results in high levels of anxiety. The anxiety produces the obsessive-compulsive behavior demonstrated in the above code snippet.
I have a solution to propose. The using keyword only works well for the passive-aggressive types (I’ll dispose the object, but only as a side-effect), while those calling Dispose or Close explicitly do so with a clean and clinical approach – there is no emotion involved.
What the IDisposable interface needs is a method that promotes self-efficacy in a developer. A method name that can stir up primal urges as the developer types. What we need is a method like die_you_gravy_sucking_pigdog() from BSD’s shutdown.c module.
Now, I know this function was written back in the days when steam engines still ruled the world, but we could modernize the function by applying some .NET naming standards.
sqlConnection.DieYouGravySuckingPigDog();
Can you feel the passion behind this statement? This statement carries the emotion that is hard to find in today's code. I hope you’ll support this proposal. Good people will be able to sleep at night once again.
There is nothing like an upcoming release to knock you out of a blogging rhythm.
Last minute tweaks. Build engines humming. Hours feeling like minutes.
The growing anticipation to give the baby a spank on the butt and send it out into the cold, cruel world of users, customers, clients.
Then of course, the best part. The sigh of relief. The satisfaction of a job complete. The …
Oh, crap.
Be right back.
Here is a solution you can use in rare circumstances and with caution. Comments are more then welcomed.
Anytime someone approaches me with a design requiring the creation of a server side process – I flinch. Spinning up a process on the server is something to avoid. Having said that, I found it useful in a small Intranet applications (a build engine) to put a web service / ASP.NET interface in front of some command line CM tools. The interface is available to a small number of trusted individuals.
I wanted to run these processes with the identity of the client, but this poses a problem. The Process class in System.Diagnostics can start a new process, but the process always inherits the security context of the parent process. Even if the ASP.NET thread invoking the Start method is impersonating a client, the Process still starts with the ASP.NET worker process credentials.
Enter .NET 2.0, which includes the User, Domain, and Password properties on the ProcessStartInfo type. In .NET 2.0 you can start a process under a different set of credentials. The catch is having a password to give. I don't want the burden of managing passwords when there is a domain controller handy.
Having found no solution in the framework, the next step was to look into the Win32 API. There are four basic functions to start a process: CreateProcess, CreateProcessWithLogonW, CreateProcessAsUser, and CreateProcessWithTokenW. Since CreateProcess doesn’t allow an alternate identity, and one of 11 parameters to CreateProcessWithLogonW is a password, those are both out of the running.
The remaining two (CreateProcessWithTokenW and CreateProcessAsUser) both accept a token instead of a username and password, so these look promising. CreateProcessWithTokenW allows greater fine tuning of the logon type and creation flags, which I didn't need, so I focused in on CreateProcessAsUser.
CreateProcessAsUser accepts a user token as the first parameter. We can easily get a token representing the client we are impersonating using the WindowsIdentity class in .NET, but there still exists a problem. The token will be an impersonation token - which isn’t good enough for CreateProcessAsUser. However, the docs mention that you can use DuplicateTokenEx to convert an impersonation token into a primary token. The code at the end of this post demonstrates the incantations.
Note: The calling process needs SeAssignPrimaryTokenPrivilege and SeIncreaseQuotasPrivilege privileges, which the NETWORK SERVICE account has by default on Windows 2003.
Note: I’m not sure how far the ‘primary token’ yielded by DuplicateTokenEx can go. Could I effectively delegate without delegation enabled? I wouldn't think so. Must experiment. Anyone know?
Note: If you are thinking of using this to launch an interactive GUI application on the server – don’t. The process will start in a non-interactive window station and remain invisible but consuming memory.
private void CreateProcessAsUser()
{
IntPtr hToken = WindowsIdentity.GetCurrent().Token;
IntPtr hDupedToken = IntPtr.Zero;
ProcessUtility.PROCESS_INFORMATION pi = new ProcessUtility.PROCESS_INFORMATION();
try
{
ProcessUtility.SECURITY_ATTRIBUTES sa = new ProcessUtility.SECURITY_ATTRIBUTE();
sa.Length = Marshal.SizeOf(sa);
bool result = ProcessUtility.DuplicateTokenEx(
hToken,
ProcessUtility.GENERIC_ALL_ACCESS,
ref sa,
(int)ProcessUtility.SECURITY_IMPERSONATION_LEVEL.SecurityIdentification,
(int)ProcessUtility.TOKEN_TYPE.TokenPrimary,
ref hDupedToken
);
if(!result)
{
throw new ApplicationException("DuplicateTokenEx failed");
}
ProcessUtility.STARTUPINFO si = new ProcessUtility.STARTUPINFO();
si.cb = Marshal.SizeOf(si);
si.lpDesktop = String.Empty;
result = ProcessUtility.CreateProcessAsUser(
hDupedToken,
@"",
String.Empty,
ref sa, ref sa,
false, 0, IntPtr.Zero,
@"C:\", ref si, ref pi
);
if(!result)
{
int error = Marshal.GetLastWin32Error();
string message = String.Format("CreateProcessAsUser Error: {0}", error);
throw new ApplicationException(message);
}
}
finally
{
if(pi.hProcess != IntPtr.Zero)
ProcessUtility.CloseHandle(pi.hProcess);
if(pi.hThread != IntPtr.Zero)
ProcessUtility.CloseHandle(pi.hThread);
if(hDupedToken != IntPtr.Zero)
ProcessUtility.CloseHandle(hDupedToken);
}
}
ProcessUtility…
public class ProcessUtility
{
[StructLayout(LayoutKind.Sequential)]
public struct STARTUPINFO
{
public Int32 cb;
public string lpReserved;
public string lpDesktop;
public string lpTitle;
public Int32 dwX;
public Int32 dwY;
public Int32 dwXSize;
public Int32 dwXCountChars;
public Int32 dwYCountChars;
public Int32 dwFillAttribute;
public Int32 dwFlags;
public Int16 wShowWindow;
public Int16 cbReserved2;
public IntPtr lpReserved2;
public IntPtr hStdInput;
public IntPtr hStdOutput;
public IntPtr hStdError;
}
[StructLayout(LayoutKind.Sequential)]
public struct PROCESS_INFORMATION
{
public IntPtr hProcess;
public IntPtr hThread;
public Int32 dwProcessID;
public Int32 dwThreadID;
}
[StructLayout(LayoutKind.Sequential)]
public struct SECURITY_ATTRIBUTES
{
public Int32 Length;
public IntPtr lpSecurityDescriptor;
public bool bInheritHandle;
}
public enum SECURITY_IMPERSONATION_LEVEL
{
SecurityAnonymous,
SecurityIdentification,
SecurityImpersonation,
SecurityDelegation
}
public enum TOKEN_TYPE
{
TokenPrimary = 1,
TokenImpersonation
}
public const int GENERIC_ALL_ACCESS = 0x10000000;
[
DllImport("kernel32.dll",
EntryPoint = "CloseHandle", SetLastError = true,
CharSet = CharSet.Auto, CallingConvention = CallingConvention.StdCall)
]
public static extern bool CloseHandle(IntPtr handle);
[
DllImport("advapi32.dll",
EntryPoint = "CreateProcessAsUser", SetLastError = true,
CharSet = CharSet.Ansi, CallingConvention = CallingConvention.StdCall)
]
public static extern bool
CreateProcessAsUser(IntPtr hToken, string lpApplicationName, string lpCommandLine,
ref SECURITY_ATTRIBUTES lpProcessAttributes, ref SECURITY_ATTRIBUTES lpThreadAttributes,
bool bInheritHandle, Int32 dwCreationFlags, IntPtr lpEnvrionment,
string lpCurrentDirectory, ref STARTUPINFO lpStartupInfo,
ref PROCESS_INFORMATION lpProcessInformation);
[
DllImport("advapi32.dll",
EntryPoint = "DuplicateTokenEx")
]
public static extern bool
DuplicateTokenEx(IntPtr hExistingToken, Int32 dwDesiredAccess,
ref SECURITY_ATTRIBUTES lpThreadAttributes,
Int32 ImpersonationLevel, Int32 dwTokenType,
ref IntPtr phNewToken);
}
A couple people have left comments here about running into the following error in Reporting Services:
The underlying connection was closed: Could not establish secure channel for SSL/TLS.
There are a number of reasons this error can occur, so it will involve some digging to get to the root cause. Most of the problems revolve around a failure to verify the SSL certificate on the report server. You might see the error because:
A good way to troubleshoot is to browse to the report server with Internet Explorer (https://machinename/reportserver) and see if IE complains.
There are also some less obvious reasons the error might occur. For example, the system time might be incorrect - making it appear as if the certificate has expired (when it has not), or has not yet been created (a certificate from the future!). Set the clock correctly.
If IE likes the cert, there might be a configuration issue. The URL used by the Report Manager to communicate with the ReportServer is found in a file under the Report Manager directory by the name of RSWebApplication.config. Find the <ReportServerUrl> element and make sure the URL matches the name on the certificate (which will match the name of the server). Hint: using https://localhost/reportserver is not going to work.
If you are in an environment that does not require SSL, and you are 100% positive you want payroll reports to cross the ether in plain text, you can find the RSReportServer.config file in the ReportServer directory and set SecureConnectionLevel Value=”0”.
I’m sure this is not the exhaustive list, but hopefully it will help someone out.
My impatience rushed me into tempting fate. Instead of waiting for the full moon to rise tomorrow evening, I started my BizTalk install Sunday afternoon. (There is also a lunar eclipse tomorrow evening - a blood moon, visible everywhere except from Australia - sorry John).
I’m happy to say the installation went swimmingly. The HelloWorld Orchestration sample spit out an invoice, and much celebration ensued.
So, what are the keys to a successful BizTalk installation? Let me tell you what worked for me.
1: Read John Elliot’s incredibly clever and Shakespearean inspired ‘Installing BizTalk 101’. (Sorry, that link is no longer available. Perhaps John will send me the contents....)
2: Follow Luke Nyswonger’s “The QuickStart Guide to Installing & Configuring Microsoft BizTalk Server 2004” step by step. Let me quote prose from Ray Bradbury’s well-known short story “A Sound Of Thunder”:
Stay on the path. Don’t go off it. I repeat. Don’t go off it. For any reason! If you fall off, there’s a penalty.”
The following steps are optional, but comforting if you are the suspicious sort.
3: I took Anil’s suggestion and added a candle to the mix. The candle I used carried the delicate scent of a blueberry harvest. I feel fairly confident in recommending any fruit scented candle, but avoid vanilla, cinnamon, and wild honysuckle.
(Note: be sure not to use those cheap little birthday candles because the process takes a while. You’ll need to sustain a good 3-6 hour burn. Also, remember to check the fire codes first if you are conducting the ritual inside a place of business).
4. I also placed a small Ganesh (remover of obstacles) figurine on the target machine. Figure 1 will give you an idea of how to arrange the candle and charm to maximize your chances of success.
Figure 1: BizTalk installations should place candles on the left - good luck charms on the right.
P.S. In all seriousness, thanks to Lee and Luke for offering to help out and giving me some pointers.
OdeToCode by K. Scott Allen
Subscribe
Twitter
Search
About
Learn C#