asked
Logon failure on SQL Server
I'm running MSSQL 2005 on Windows Server 2003. I have a Crystal Report wrapped into a tiny VB executable that loads the report to a viewer and permits the user to supply parameters. All of this is fine. My network admin has granted server access to domain users, and the report is configured to use Integrated Security. But, I keep getting logon failure messages. The server security logs indicate a sucessful login of the machine and my test user, but the app returns a 'Logon failed' message. The domain users group is a logon and user on SQL and the relevant database, the 'public' role has read permission, and I'm banging my head against the wall. Unfortunately, the landlord just stripped all the carpet off the walls here. Interestingly, I get the same results when I try to use my logon either as a domain user or as an admin on the server. I'm convinced the answer is simple, once I find it, but database administration is not my strongest skill and the answer eludes me. Help?
If you have a way to modify your VB code, try to add a small dummy bool method there which tries to connect to the database with SSPI, opens the connection and this this succeeds, closes it and returns true (false otherwise). This will allow you to pinpoint whether the problem is caused by VB or by Crystal. Somehow I don't believe that it is VB, but still it is a good idea to test with a dummy method just for sanity check. While Crystal is loaded into VB process space meaning that the only configuration file available to the app is the one created by VB, it is still possible that whatever connection is tattooed in Crystal brain during past development does not get correctly reset to point to the correct server. Crystal is known to persist connection information info in the report file itself, which requires the wrapper code to forcefully reset it at run time. Oleg
@Oleg - Crystal sounds like they want to re-invent everything, always fun to come up with work-arounds
@Blackhawk-17 Yeah, Crystal is a perfect tool to remind us that life should not be easy. It should also be the best candidate for that new MSDN magazine column named "Don't get me started" by David Platt :)
http://msdn.microsoft.com/en-us/magazine/ee532098.aspx?sdmr=DavidPlatt&sdmi=authors
I am having difficulty believing it's _my_ VB code - all I do is use the Report Document model to configure and load the report into a Crystal Reports Viewer. There's simply nothing there to make a mess _with_! The auto-generated code, that could be another story, but looking at the partial class file, there's nothing there but declarations and layout info. My Crystal connection is pretty straightforward - stipulate the server, database, and Integrated Security. Might it be the SQL Native Client? I'm not well acquainted with the differences among these, but there are some options. I'm working on the dummy routine but won't finish it before I have to leave today. Friday is a short day here and I have to leave promptly. Thanks all for your commentary and suggestions. Please don't stop them. I WILL beat this thing. Maybe with a baseball bat, but I will beat it.
There are many things Crystal does to "***enhance developer experience***". For example, if you have a param in Crystal which is implemented as a dropdown list, such list is inevitably populated with (possibly garbage) values from the dev database. This is cool, so you get the list while debugging at dev time. The trouble is at run-time in production the list is not refreshed unless you implement some sort of **jumping through the hoops** routine which empties the list, forces the connection info to refresh and repopulates the list. Same applies to the connection. And since you mention SQL Native Client, who knows whether the version of Crystal you have fully supports it (and yes, there are so many of them versions). The worst I had to do in the past is to create ODBC(yuk) entry on the box but make it invalid by setting no valid password because ODBC stores passwords in registry in clear text. At run time, I would simply feed credentials details (uid and pwd from config file) for ODBC entry with the same name thus achieving the switch I needed. Beware about SSPI connection though as they destroy the pooling logic which otherwise is managed so efficiently by SQL Server.
Oh, boy. Now I think I'm in WAY over my head. By education I'm an ocean engineer, not a code geek, and what I've done I've sort of picked up in odd bits trying to work this assignment. My report uses some dropdowns for report parameters, but I was not messing with the connection offerings other than stipulating the user name and password. I've changed those, and the Integrated Security settings, since then, trying to figure out a combination that will work for someone other than me, on some other machine, but so far no joy. So, I have a not-very-complex non-embedded Crystal Report that I need to wrap in VB code to make an executable file. It's not working for login even when I use an Admin account. The logs tell me that it's getting onto the machine, so it sure looks like it's failing at the database. What I can't figure out is why, and the logs are not helping any. This can't be impossible to do, can it?
I've made enough progress to know that it's a permissions issue on SQL Server, not something in my code. Now the task is to grant the proper permissions to all the proper grains in this granular security arrangement.
