Changed to Windows Authentication - ADP db no longer connecting (1 Viewer)

I think I may be in trouble here.

I've got an adp that's connected to tables on Sql server 2000. I've changed the server over from mixed mode to windows authentication only and the adp no longer opens. I've added logins under SQL security. I've given each individual user access to the Database in question. I've altered the odbc connection.

When I open up a new db and try to link a table on the server using the altered odbc connection I can only see
dbo.dtproperties
dbo.sysconstraints
dbo.syssegments

Any help would be much appreciated.


I set up a new user group and added all the relevant users and gave the group access to all tables view and sprocs but this didn't resolve the problem.
I've changed the SQL Server setting back to mixed mode pending further investigation.

P

Hi there,

I will need you to do some trouble shooting for me so I can help:

1. Can you check the settings in your adp under file/connections, tell me what they are.
2. Can you connect via query analyzer to the database and test permissions with windows accounts? post back the result.
3. run sp_who in Query analyzer
4. run select * from sysxlogins in Query analyzer
5. run select * from sysusers (on the database concerned)

When you said you added the users to a group, was it an active directory group or sql server group?

Is this server in the same domain as active directory?

Hi SQL_hell,

I think you hit the nail on the head with point 1. It seems an obvious thing to check but I had this change over in authentication settings thrust on me a little sooner than I was ready for.

1) In File - connection Log on was not set to Integrated Security.
2) I can't check windows permissions with windows accounts as I've changed back to mixed mode. I'll have to switch over and check after hours.
3) There's nobody currently logged into the db appart from myself in QA.
4) Returns list of users that I set up
5) Returns db roles, the new role I set up and users.

I added a new role to the SQL DB and added the users to that role and assigned permissions on all the relevant tables, views etc to the role.

Thank a lot for your help. As I said I'll try to change over the auth settings again after hours and let you know how I get on.

Peter

Hey,

Glad I could help, just for future refernce you can check windows permissions with mixed mode, mixed mode means that you can use windows logins or sql server logins, windows authentication mode means you can only use windows accounts.

Good luck and post back if you have any more issues and I will be happy to help