This will not be a technical blog post, so if you’re not in too all this insightful feelings stuff, close your browser now.
I do a lot of coding, but I’m not a developer. I do a lot of system installation and configuration, but I’m not a System Administrator. I do a lot of system designs but I’m not a system architect. So what am I? People who have known me for a while will know I would describe myself as a very talented bug finder and troubleshooter. I’m also very stubborn both in a good way and in a bad way. So let’s have a look at how “I can be oh so wrong” and make a complete fool out of my self 🙂
I was asked to try and implement SuperOffice 7 in the hosting environment I’m working on. As always, we had known for quite a while this was something we would need to get working, but when I finally got the assignment I had less than a week to get something up and running, because we had a potential new customer that wanted a solution NOW! (Why is it always like that?)
So I asked for the installation media and created a proof of concept installation. It had a few errors and flaws but in general I was convinced I could get this up and running. I prefer automating everything I do, while I’m doing it, to make it easier to reestablish the environment and to “catch” whatever problems I will run into, when I need to automate it anyway, once we have to go into production.
So after 4 days I had the following issues, in order to give the final go on my solution.
- I couldn’t automate the process of creating a new blank SuperOffice database
- I couldn’t get SuperOffice to start up inside App-V but was working fine on my test servers and deployment machine, so was convinced this would be easy to solve.
- While I was converting my ugly PowerShell scripts to PowerShell CmdLet’s I noticed it was using insane amounts of memory that I was worried would impact the performance of the provisioning engine.
Except the creating of the SuperOffice database everything was automated so a Support case was opened on the 2 upper problems. While testing and trying out different things I noticed something funny in my PowerShell snap in. Sometimes I would get the same error message “Failed to create Identity from tokens” as I got on the SuperOffice client . While digging in a little deeper I realized that I really hadn’t done my homework properly. The NetServer API SuperOffice offers to .NET developers doesn’t allow you to “swap” between databases and the only way to work around that would be to place the code inside different AppDomains and I had never tried coding something that used different AppDomains. I hated that, and started getting frustrated at SuperOffice, which was completely wrong of me for 3 good reasons. For one they had a valid reason for making that choice, and two I learned a lot of new exciting stuff about working with AppDomains that I know will be useful to me in other cases and lastly. Being able to unload all the SuperOffice stuff from within the memory space of the provisioning engine, also solve one of the 3 problems mentioned above.
So 1 weeks passes and I just can’t “crack” the problem with the SuperOffice client and SuperOffice wasn’t helping us out a lot, on the case. I could reproduce the error in almost any environment ( Windows 7, Windows 2008, and Windows 2008 R2 servers. From 32 and 64 bit. With and without App-V. With or without being run from Remote Desktop or XenApp desktops. I tried using SQL 2005, SQL 2008 and SQL 2008 R2 servers … nothing would work for me, but everyone I talked with kept saying “but it works for me, no problem. It sure sounds weird” ) , so a colleague put me in touch with a partner we use when implementing SuperOffice at other customers. They solved the database problem in a few hours and we sat down and went through the whole setup to see if we could figure out what I was doing wrong in regard with the SuperOffice Client. While showing my approach for different things they were complaining a lot. I wasn’t doing things the “SuperOffice” way. I refused to use “CRM5” as Schema prefix and insisted on keeping DBO as owner on all objects (will give problems when working with 3rd party add-ons). I used a controversial way of loading SuperOffice by using inipath as start parameter instead of keeping the configuration inside the SuperOffice client installation folder ( will give problems with reporting, office add-ons and 3rd party add-ons). And I refused to add the user’s as SQL users in the database. ( Remember, I am stubborn. and I had read somewhere you didn’t need to do it. ) Any way, he suggested I added the users ( as windows users ) to the database and BAM, everything worked, I mean everything. I was so embarrassed. Anyone who have worked with SuperOffice know this is how you do things, and people had been telling me nonstop since I started on this project I had to remember this. But I was so convinced this was not needed, but I couldn’t remember where I had read it. I just made sense to me, why add the user, when SuperOffice already had a username/password from the superoffice.config . And the NetServer API worked flawlessly without doing that. We had even priced the product to not include an SQL CAL for each user.
So just be sure I sent an email to a contact within SuperOffice asking him to confirm, that this had been my problem all along. The reply I got back wasn’t quite what I had expected. He told me I was correct, users do not need access to the database and the problem was with the ODBC DSN links I had created. I was about to explode at that point. I felt I was bouncing back and forward all the time and getting nowhere. I mean there is only one way to create a DSN link, you choose an ODBC provider fill in server and database and you are done. How freaking hard can it be.
Moving on, we decided to stick with adding the users to database and just be done with it. While restoring a customer’s data from one SuperOffice database too another we started getting some access denied messages. As always my first tool of choice to trouble shoot SQL problems, was opening SQL Profiler and look at what was going on. I noticed dbsetup.exe was executing half the queries as the windows user we were logged on as, and not the SQL login it was being told to use. The Support Technician from SuperOffice say; as it was the most natural thing in the world “Did you remember to set the DSN connection to use SQL login” ?
God . If I hadn’t already felt dumb enough as it was, that sure did the trick. Why, oh why hadn’t I opened the freaking SQL profiler a long time ago while trouble shooting the client problems ? Why didn’t I listen when SuperOffice told me to check the DSN connection ?
If you look at this screen shot. Since the username and password doesn’t get saved, but it affects the “test” button at the end of the wizard, I have always assumed this only affected the test in the end. But that is defiantly not the case. If you leave it as show, it will add a “Trusted_Connection” entry in registry with the value of “yes”. If you change the radio button down to SQL Authentication it doesn’t add that registry entry, or it changes the value to “no”
Most connection strings I have worked with have always had Trusted Connection=yes. I generally try and avoid using SQL login when I can (but it can be useful in some cases) What happens in the case of SuperOffice is, they don’t specify that when they connect using the DSN, and therefor “inherits” whatever setting you choose in the wizard. That can be quite handy, and confusing at the same time. But it means that even thou SuperOffice send a username and password, it gets ignored if Trusted_Connection is set to yes, flip it back to SQL and it will now connect using the username and password SuperOffice sends with it.
Anyway . . .So I went back and changed all my code and scripts to create the DSN links with Trusted_Connection set to “no” and removed the all users from the Windows group I had created to give users access to databases and everything worked like a charm. So now we don’t need to bill all users for an SQL CAL, but only one CAL per SuperOffice installation.
Who would have guessed, not only do I make mistakes I can also be wrong.