I am trying to setup logins on the SQL database so that users can access the lawson tables to do queries via excel and create reports via crystal. I keep running into the login failed for user error. Any thoughts?

Are you adding access for the domain user or are you adding users locally on the DB ?

I'm trying to add a network user or user group.

I think what Tan is asking is that starting with Sql 2000 you could do multiple instances of Sql Server and authenticate via a Domain ID or Group -- is that what you are trying to do?

Or are you going into Enterprise Manager and creating new users in the sql security tab?

I've actually tried both. But my main intention is to add a domain group to the logins and grant datareader and denydatawriter roles to the group. I do have the SQL Instance setup in mixed authentication mode.

It's been a while since I did security for Sql so I'm going to ask some basic questions. When I tried doing it the domain way I usually had to setup an alias on the client pc so that it would like this <domain>\<instance> before I could get people to get in? Have you tried something similar?

no I have not, I'm not quite sure what you mean by alias on the client pc.

There is a tool that get's installed when you install the Sql client on a PC. It's a called "sql server client network utility". What you can do with this is create an alias to your sql server and select whatever network protocol for communication, i.e. named pipes or tcp ip.

So once you start the tool the tab you should see is general and the network protocols that you want to use on the right hand side.

The second tab is the alias tab. This is where you can define the alias to the sql server. You can select the network protocol (usually tcp/ip) and then server name and port number.

So what you would do for the server name (not the alias name) is to put <sql server>/<instance> or <domain>/<instance>. And if the port is different you can manually put it in or if it's default then just check the dynamically determine port. This should allow you to authenticate to the domain.

Then go to the odbc panel to create the odbc dsn and select the alias you created above as the database you want to connect to.

Thanks, I'll try it out. I appreciate all you time and knowledge.

:banghead:

AREA51, you are great, thanks. Worked like a charm.

Thanks. glad it worked for you.

I was thinking there was another too in which you would have to create domain groups and put those groups in the server with rights to Sql Server. But if this works than thats good too.