I am getting the above error on a SQL call to populate a recordset.  It is retrieving data from 2 custom tables on a custom form.  The SQL query works fine in SQL Mgmt Studio, but not in SLX.  

This is also an upgrade to 64x system SQL 2008 and SLX 7.5.3.4233.  The previous SLX had a number of customiztions.  

I have been unable to find any answers to this.  

Anyone have any ideas. 

Thanks

Tim

.  

1. Make sure that SQL is set to Mixed Mode Authentication

2. Make sure you have a sysdba user defined on your new server and it has access to the database

3. while logged in to Query analyzer (either as SA or Windows Integrated) run the following
sp_change_users_login 'update_one', 'sysdba', 'sysdba'

4. In your connection manager - on the all settings tab, select Integrated Security, then Reset. Select Persist password, and set to true. Note: Do NOT TEST the connection after doing this step, or it will reset.

5. Report back here

ws

And of course double check that the correct SLQ Server Native Connection client is installed on each SLX application machine...

The first three suggestion are either true or have been tried.  

Suggestion 4 seems is the only one I'm not sure on.  

None of the windows user logins will open SQL with Windows Authentication except the Domain Administrator.  Here is what I have so far

1.  The query that is failing in SLX code DOES work when run in a SQL Mgt Studio query window

2.  The same query does NOT run in Execute SQL in SLX Aministrator and generates the same error, "The Server Principal 'sysdba' is not able to access the database 'DATA" under teh current security context."

3.  Only 1 user can log into SQL Mgt Studio with Windows Authentication, that user is the Domain Administrator

4.  While logged in to Windows as the Domain Administrator, logging into SLX (with the SLX admin or other SLX users) still fails.  

5.  All other standard SLX SQL calls work fine and the rest of the program runs fine, only the SQL Calls from the Custom Tables on the Custom Form.  

Completely studk.  Again, it is 64X, that might be something.  But it's something in SLX because the calls work.  

Anything else I might look at.  

Thanks

Tim 

Are you able to log in to your server as sysdba?

 I just confirmed that yes, I am able to login to SQL Server Management Studio using the sysdba user via SQL authentication from the  client computer.

And who owns the custom tables?

BTW, SQL Server Native Client is REQUIRED for all SalesLogix Connections.....regardless of what you think SLX is doing for connectionS (that's plural) to SQL Server.

Thats MANDATORY, 100% Must Have, ALWAYS, etc.

But since you are having trouble with only a few tables......than you have a SQL Server Native Client installed on this box.

sysdba owns the tables, just checked to be sure.  

Thanks

1. Did you create your custom tables from within SLX, or via SQL? (If SQL - then you will have issues)

2. Can you post the query it is having problems with?

Actually this is a system that has been running for many years on Server 2000 and SQL Server 2000.  The database was backed up on 2000 and the backup restored on 2008 64x.  

So it has been working up to the point of restoring on new server.  

But I wonder if the table were copied to a backup table, a new table created using SLX, then repopulated from the backup.  It would seem that might work.  At the very least, I could create an empty table with the identical structure, try to query it from SLX administrator and see if the error occurs on the new table.   If no error, this strategy might work.  

Thanks

T

One more thing to check first.

Make sure that you have SQLNCLI10 installed on the workstation.

SQLNCLI is vintage SQL2005, but is easy to get mixed up.

As RJ alluded to.... version mismatches can cause weird results.

ws

Quote: Originally posted by Tim Conway I am getting the above error on a SQL call to populate a recordset.  It is retrieving data from 2 custom tables on a custom form.  The SQL query works fine in SQL Mgmt Studio, but not in SLX.   This is also an upgrade to 64x system SQL 2008 and SLX 7.5.3.4233.  The previous SLX had a number of customiztions.

I have read this thread and noticed that:

a) You are able to login into SalesLogix.

b) You are able to execute the Query outside SalesLogix.

c) Your query includes Data from Tables on Multiple Database.

Based on the Error message, it looks as if you are trying to Combine multiple databases into the SQL Statement: sysdba is unable to access the database "DATA"

The error indicates that your Query includes references to a separate Database and that sysdba does not have permissions on that database.

Here are some tips to troubleshoot this:

a) When testing Statements for SalesLogix within SQL Mgmt. Studio, make sure you are logged in as sysdba. You could have multiple connections to the Same Server on the Same Instance, so you need to pay attention to the status Bar of the Query window to make sure you are executing as sysdba  (and I rather you do this than relying on SETUSER or similar as then it may not be obvious what User Context you are under)

b) Verify that sysdba has access to the Database and the Tables (and/or views) used in your Query.

c) The SLX Provider is quite picky and may complaint about some SQL Statements. Some queries may run all day long on SQL Mgmt. Studio and sometimes even on Administrator, but may fail to run when executed by a USER as SLX applies the Security Joins. So, you may want to wrap Complex Statements into Views and then Enable those within SalesLogix.

Hope this bit of info will help you out.

Season's Greetings!

Thank you everyone for your help.  It is finally fixed.  All the suggestions were in the right direction and each one could form a nice check list.  Sysdba did have security rights on DATA as well as the SLX database, but something was still not right.  Here is the what corrected things

I completely deleted any reference to the SYSDBA user from each of the Macola SQL databases and SalesLogix, recreated it, reconfigured security, and ran the sysdbafix script. After doing this I was able to successfully log into SalesLogix on the HP2 computer as any of the Users and did NOT receive any errors. 

All's well that ends well.  

Thanks for everyone's help.  

Tim