SQL 2005 user security for JDE

Hello All,

We are working on an upgrade from SQL 2000 to SQL 2005. We have a very vanilla Intel install of ERP8. We upgraded our development server which has both the development database and JDE logic for DV7334. All is good in JDE and RDA, FDA, and TDA --- until I try to generate a custom table. It gives me a login failure on the testdta user. I know that SQL2005 has schemas, but I was not aware that we had to make changes to security. Am I misinformed?

I can login to the SQL server instance as TESTDTA.
I did test the ODBC connection from the fat client using the TESTDTA id successfully.

I know I need to study up on SQL 2005. I just have a project I was hoping to test on the DEV server this week to get in place before Christmas.
Thanks for the help.

Jer

Hi,

Check that PUBLIC has the right to create tables on your
DEV database.

[ QUOTE ]
Hello All,

We are working on an upgrade from SQL 2000 to SQL 2005. We have a very vanilla Intel install of ERP8. We upgraded our development server which has both the development database and JDE logic for DV7334. All is good in JDE and RDA, FDA, and TDA --- until I try to generate a custom table. It gives me a login failure on the testdta user. I know that SQL2005 has schemas, but I was not aware that we had to make changes to security. Am I misinformed?

I can login to the SQL server instance as TESTDTA.
I did test the ODBC connection from the fat client using the TESTDTA id successfully.

I know I need to study up on SQL 2005. I just have a project I was hoping to test on the DEV server this week to get in place before Christmas.
Thanks for the help.

Jer

[/ QUOTE ]


Check who has create table permissions in your database:

<font class="small">Code:</font><hr /><pre> use jde_development
select sys.database_principals.name, sys.database_permissions.grantee_principal_id,
sys.database_permissions.type, sys.database_permissions.permission_name, sys.database_permissions.state_desc
from sys.database_principals
INNER JOIN sys.database_permissions on sys.database_permissions.grantee_principal_id = sys.database_principals.principal_id
where sys.database_permissions.type = 'CRTB' </pre><hr />

You should see that (at least) public has CREATE TABLE permission.

If public doesn't have that permission, chances are it doesn't have the other necessary permissions.


Run this to grant the default E1 SQL permissions:

<font class="small">Code:</font><hr /><pre> use JDE_DEVELOPMENT
exec sp_MSforeachtable "grant Select, INSERT, UPDATE, DELETE, REFERENCES on ? to public"
</pre><hr />

Do the same for other databases.

Here is some background that you might find helpful on how E1 SQL security works:

http://jeffstevenson.karamazovgroup.com/2009/08/enterpriseone-sql-security.html

Hi Jer,

Some basic things which you might have already tried out, 1: In SQL 2005 the passwords are case sentitive so when generating a table please ensure you have put the password with the correct case.
2: Make sure your deployment server is mapped while generating tables.

Can you post the jde.log.

Regards,
Joel

Thank you for the reply Sebastian.

Public does have create rights as far as I can tell.

Jer

Jeff,

Thank you for the scripts. I ran them and found that public did have create. I ran the second one anyway and still no luck.

Jer

Hi,

Is your TESTDTA login password other than TESTDTA?
If so, I remember that there was a password section to
add to your Deployment and FAT JDE.INIs

Joel,

I tried table generate with both upper and lower case passwords with no luck.
I am not sure what you mean by the deployment server mapped. Can you please clarify what I should be looking for?

Attached are the jde and debug logs.

Thanks for your assistance.

Jer

The first attachment is the jde.log; this post has the jdedebug.log.

Jer

[ QUOTE ]
Joel,

I tried table generate with both upper and lower case passwords with no luck.
I am not sure what you mean by the deployment server mapped. Can you please clarify what I should be looking for?

Attached are the jde and debug logs.

Thanks for your assistance.

Jer

[/ QUOTE ]



From the looks of the jde.log, the E1 login/SQL login cannot connect to jde_development.

SQLDriverConnect failed. DSN: Business Data - TEST

Are the ODBC's correct? Updated to SQL Native Client?

If they are correct try the following:


See if your SQL logins (TESTDTA, etc.) are users in the databases in question:

<font class="small">Code:</font><hr /><pre> use jde_development
select * from sys.database_principals </pre><hr />

Yes, the ODBCs are updated to SQL Native Client matching the database Service Pack 3. I can connect successfully from the fat client using TESTDTA on the Business Data - Test and Central Objects - DV7334 ODBCs.

Is there another test to try?

Thanks for all the help.

Jer

Sorry, forgot to mention that I ran the query you provided on both JDE_DEVELOPMENT and JDE_DV7334 and all the JDE users showed up as expected.

[ QUOTE ]
Yes, the ODBCs are updated to SQL Native Client matching the database Service Pack 3. I can connect successfully from the fat client using TESTDTA on the Business Data - Test and Central Objects - DV7334 ODBCs.

Is there another test to try?

Thanks for all the help.

Jer

[/ QUOTE ]

So you can login to E1 in DV7334 and bring up data?

The only problem is when you try to generate a table? Can you generate a table by logging in to SSMS with the TESTDATA account?

[ QUOTE ]
Sorry, forgot to mention that I ran the query you provided on both JDE_DEVELOPMENT and JDE_DV7334 and all the JDE users showed up as expected.

[/ QUOTE ]

Try running this to see if you have any orphaned users:

<font class="small">Code:</font><hr /><pre> use jde_development
GO
sp_change_users_login 'Report' </pre><hr />

I ran the query and it returned no rows, which I expect is good from a database standpoint, but bad from a finding something to fix standpoint.

Are there other things to try or are we getting to the point where a DBA needs to work through our setup with a fine toothed comb?

Jer

Hi,

Could you try the below steps and let me know the outcome so that we have eliminated one possible cause

1: On your fat client map your deployment server i.e. Start - > Run -> deployment server name

2: Login to E1 in DV and do a GET for that custom table

3: Now try to generate the table

Joel,

Same error. I did start run \\weaver6\B7334 which worked. I did a Get in OMW, but still have the same error.

Jer

Just thought I would post the resolution in case anyone runs into this situation.

Apparently, SQL Server 2005 is case sensitive on Login passwords and our passwords in SQL 2000 must have been lower case. Once we retyped the passwords in upper case, the problmes went away.

Jer