Recently when configuring a JSP page based on Venkat’s walk-through of linking an HFR report to an OBIEE answers report we bumped into the following error when trying to access our SQL Server database. It turns out this error is caused when TCP/IP to the SQL Server server instance has been disabled. It is actually disabled by default. Below are the steps to resolve the issue. Once we’ve enabled TCP/IP our JSP page using the JDBC call worked like a charm.

Here is the error:

Read the full story

Typically I haven’t bumped into this error in the past with OBIEE but the other day it smacked me in the face. 

Problem
I was adding a SQL Server 2005 datamart to my Physical layer and didn’t run the initial check to see that OBIEE could read the data in my view/table objects. Clearly it was able to connect to the server and bring over the pertinent DB objects. However, when I right-click on a table or view object in the physical layer and select “View data…” I get the error prompt above.  The error reads something like ODBC error state: 37000, ODBC error state 50002, [NQODBC][SQL_STATE:HY000]….A general error has occured.

Solution
Figuring this problem out was not that difficult. The error prompt above is actually produced when the “Require fully qualified table names” checkbox is not checked.

To get rid of the error and to allow OBIEE to correctly read the data source in Answers, etc. follow the instructions below.

1. Right-click “Connection Pool” for the database in question.
2. Under the “General’ tab ensure that “Require fully qualified table names” checkbox is checked
3. Click “OK” button and close the window.
4. Right-click any table or view object you previously had a problem with and select “View data…”
5. Enjoy

A while back I was working with a client on a web application project that had a basic SQL Server database as the relational data storage system. They wanted to use a rather “locked-down” SQL Authentication mode for the credentials that would allow the web app to communicate with the database. This user had read/write ability only. However, the web application would access the database mainly through use of stored procedure calls. As we all know, if a read/write user is going to communicate via stored procedures that user must have EXECUTE permissions on each stored procedure in question. We set this up in SQL Server security and the ASP.NET web.config file of the web application and everything worked well.

We finally were requested to move their “finally” tuned developed database to their production system. So, we did. Production worked great for weeks. Then the client needed a “quick” enhancement. So, during our maintenance window we created a backup of the production database and copied it down to development to begin the work. We dropped the existing development database and restored the backup of production on the development server. We started the web application and immediately encountered problems. Eventually, we noticed that the “login” field in the user security properties was blank. This is the login name that is required by the web.config credentials and it was missing. To keep a long story short we circumvented the issue by dropping and recreating the user so the login would again be function. That was a solution that worked but it was not the right one. After more research we came across an article on SQL Server Logins and Users. Basically the problem is that the master database stores the user login information for each database user. When the database is dropped, the user still exist in the server security model and has no home to belong to. The user is basically “orphaned”.

Here is a template script to solve the issue whenever it occurs:

Use Master
GO

--// Let's see the current global system ID for this login
SELECT sid FROM
dbo.syslogins
WHERE
name = [MyLoginName]

--// User your DB in question
USE
[TheDB]
GO

--// Let's see the current global system ID for this user
SELECT sid
FROM
dbo.sysusers
WHERE name = [MyLoginName]
GO

--// Maps an existing database user to a SQL Server login. The "report" call simply lists the users and their SIDs
sp_change_users_login 'report'
GO

--// Perform the re-link
sp_change_users_login 'update_one', [MyLoginName], [MyLoginName]

--// Confirm that we are now in sync
Use Master
GO
SELECT sid FROM
dbo.syslogins
WHERE
name = [MyLoginName]

SELECT sid
FROM
dbo.sysusers
WHERE name = [MyLoginName]