NEVER Say it will only take Five Minutes…. (The case of the ILM SQL Management Agent trying to connect to a database with a trailing space in the name)
Before I begin to explain this post heading, I wanted to say a few words about my contribution to the “IDA Guys” blog. I was excited for the opportunity to participate in this blog. One of the reasons I wanted to participate is that through the years I have received help from many, many people in the community via their blogs and forum posts. They don’t know they’ve helped me because most of the time I ended up at their post via a search engine, looking for a specific answer to a question or problem. It is hard to imagine how we ever lived without community support. I really hope that through this blog others too will find some information that will be useful in getting their job done. The content of this specific post is a completely different subject from the previous posts, and the one that follows this is likely to be different yet again.
Now, back to the subject at hand….
Never say it will only take five minutes. Why? Because you never know the issues that you are going to face.
Yes, I am a consultant, so there is some suspicion when I’m asked “how long” and I hesitate and am reluctant to give an answer. But the reluctance is born from experience. I really need to think before I answer a question about the level of effort. I know I’m getting old, my kids remind me of that with every additional strand of gray hair that I proudly accumulate. I also know I’m getting old when I see younger IT professionals proudly and quickly saying, “That’s easy, it will only take a few minutes”. Call me cynical, but my personal opinion is, “Nothing is easy”. I prefer to think of myself as a realist. Except when I forget my own advice, like I'm about to relate.
Given that backdrop, I want to share an experience I had recently where I was requested to do a fairly small item with an ILM 2007 implementation. The customer wanted me to set up a simple SQL MA, which was not part of the original work plan. I figured I could get this done very quickly, the proverbial five minutes. The supposed five minute easy change, took almost a full day. In relating this experience, I also hope to drop a few bread crumbs along the way so that if you hit this problem, you may remember this post, and your five minutes won’t grow into hours.
The simple SQL Management Agent
How long should it take to create a simple Proof of Concept connection to a SQL database? Five minutes? You would think, but experience has taught me that things rarely go as planned. The company I’m working at is large and complex. The SQL database was on a remote server and is controlled by the SQL team. The developer I’m working with is six time zones away. It is a development database, so the developer does have administrative access, so getting access to the database wasn’t a problem.
Minor problem number 1 hits me when I go to setup the MA. The account I gave the developer to give access to the SQL table is not going to work. My development environment is in an untrusted domain from the target SQL server. The account I had permissions granted to is in the forest of the SQL server, but “Integrated Authentication” won’t work from the ILM development environment in the untrusted domain. I need a new SQL account provisioned, but just to be sure that was the problem I did a little research on the SQL authentication options. All in all, my five minutes is up to an hour.
That though, didn’t turn out to be the biggest problem. Once I received the SQL account, I still could not get connected, and repeatedly received the following dialog, “Failed to retrieve the schema. Cannot open the database table you’ve specified”.
The first bit of troubleshooting was to verify the account had privileges. I had no problem using that account through various other tools, including osql and SQL Server Management Studio.
I could not figure out what was going on. I was getting a bit frustrated at this point, so to completely eliminate permissions I got on the phone with the developer and we temporarily elevated the account privileges, but as expected, that didn’t work either.
I really needed to get things moving, so I decided I would just recreate the table on my local development SQL box. So I auto-generated, via the SQL Server Management studio, the creation of the table that I would then develop against, and I’d worry about the real connectivity later. Here is a snippet of the SQL generated to create the table, and see if you notice anything:
USE [Database1 ]
GO
CREATE TABLE [dbo].[TESTTABLENAME](
[USER_ID] [varchar](30) NOT NULL,
…)
Do you see the issue? There is a trailing space in the database name. I noticed it immediately when I looked at the script. I didn’t bring it up at first, because using osql from the command line, I could add or strip the space on the command line and it would still work. But when all else failed, I asked the developer owning the database to copy the table to another database on the server, and sure enough that was the issue. After that, I confirmed with my colleagues on the ILM Product Team that this would be a problem. They were able to review the code and validate that ILM trims the input from the Management Agent Setup UI. This was what was causing the connection failure.
That is the bread crumb that I hope helps somebody else some day, in the unlikely event that their database name has a trailing space.
The five minute MA creation took me the better part of the day, and the reality is we still need to come up with a solution to deal with this table in production as I was told that changing the database wasn’t going to be an option, but that is for another day.