Re: ADP/SQL Server 2000 Security Problem

The call to the stp_SecGrantLoginDBAccess procedure have been done after or
before the reattachment of the database?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC


"MikeC" <anonymous@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:N9G6e.1915$dT4.1516@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> The server is running Windows 2003. Also, the connection problem occurs
> on different client machines running Win2K and WinXP. I'll check whether
> users are connecting to the server as guests. I had assumed they were not
> logging in as guests, but that may not be a safe assumption at this point.
>
> I originally added the database by detaching it from MSDE 2000 and then
> using the Attach option in Enterprise Manager.
>
> I'll also test using a SQL Server account and see what happens. Thanks
> for the ideas.
>
>
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:%23GE0wDwPFHA.1500@xxxxxxxxxxxxxxxxxxxxxxx
>> The owner of the dbo account shouldn't have any effect.
>>
>> If the second machine is a WinXP machine, then you can check is if users
>> logon as themselves or as guests.
>>
>> You can try to set a SQL-Server account and logon with it. You can try
>> to use Enterprise Manager to setup the account and see if the domain AD
>> is recognised by EM. Finally, you can take a look at what happens with a
>> backup/restore or a detach/attach from the MSDE to the SQL-Server
>> machine.
>>
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Technologies Virtual-PC
>>
>>
>> "MikeC" <anonymous@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:i1F6e.603$J12.399@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>>> The actual user account was 'AD\Pchapin' in the example mentioned in the
>>> original post. The account names display the same way
>>> ('AD\[WindowsAccount]') in Enterprise Manager.
>>>
>>> Originally, login access was granted to individual users using a stored
>>> procedure...and it worked fine. At least, it worked in MSDE 2000. The
>>> actual stored procedure is at the bottom of this message. (Security
>>> roles were successfully assigned using a different procedure.)
>>>
>>> Also, I have not created any new accounts for the production database.
>>> We are continuing to use the accounts that were previously created in
>>> MSDE 2000. Other than SA, there are no SQL Server user accounts. All
>>> users have Windows accounts.
>>>
>>> The SQL Server and MSDE 2000 instances of the database are on separate
>>> machines. All users should have access to both machines. I believe
>>> that the users can access the SQL Server machine because it is already
>>> being used for other production databases, like Sharepoint Portal.
>>> However, I must be missing something because no one can connect!
>>>
>>> One thing that may or may not be relevant is that I was the dbo in the
>>> original database and someone else is the dbo in the production
>>> database. I still have administrator permissions, but I am not the dbo.
>>> Would this have any effect on user security? I seem to be running out
>>> of clues. Is there anything else I should check?
>>>
>>> <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
>>> ALTER PROCEDURE stp_SecGrantLoginDBAccess
>>> @LoginName nvarchar(128)
>>> AS
>>>
>>> SET NOCOUNT ON
>>>
>>> SET @LoginName = 'AD\' + @LoginName
>>>
>>> EXEC dbo.sp_grantlogin @LoginName
>>>
>>> EXEC dbo.sp_grantdbaccess @LoginName
>>>
>>> EXEC dbo.sp_defaultdb @LoginName, 'WSDTrainingSQL'
>>>
>>> SET NOCOUNT OFF
>>>
>>> RETURN
>>> <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
>>>
>>> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
>>> wrote in message news:OH7pH%23uPFHA.2460@xxxxxxxxxxxxxxxxxxxxxxx
>>>> And how did you achieve this the MSDE 2000 database? By following the
>>>> same settings, you should get the same result. My opinion is that you
>>>> have created SQL-Server User Accounts instead of Windows Accounts on
>>>> the SQL-Server. With Windows Accounts, the name of the machine should
>>>> be displayed; for example 'MyMachine\someuser' and not just 'someuser'.
>>>>
>>>> It is also possible that these users doesn't have the right to
>>>> interactively logon on this machine. However, if you have used the same
>>>> machine as the MSDE 2000 database, then there should be no difference
>>>> there.
>>>>
>>>> If you have used roles, then it's normal that individual permissions
>>>> remains unchecked for users. Only the permissions that you
>>>> specifically set for a user, independant of those granted by its
>>>> associated roles, will be displayed there.
>>>>
>>>> --
>>>> Sylvain Lafontaine, ing.
>>>> MVP - Technologies Virtual-PC
>>>>
>>>>
>>>> "MikeC" <anonymous@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>>>> news:vVC6e.553$J12.287@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>>>>>I just migrated a functioning MSDE 2000 database to SQL Server 2000 and
>>>>>now
>>>>> the non-admin users are unable to connect to the production server.
>>>>> The ADP
>>>>> (Access 2002) application uses Windows authentication mode.
>>>>>
>>>>> I can connect to the server just fine using the ADP file from any
>>>>> desktop,
>>>>> but other users are unable to connect from the *same* desktop
>>>>> machines. The
>>>>> error message is:
>>>>>
>>>>> "[MyApp] could not log on to the server. Verify that the log on
>>>>> information
>>>>> is correct."
>>>>>
>>>>> The File/Connection option does not display for these users, but when
>>>>> I log
>>>>> into the machine as myself, I can select this option. The connection
>>>>> information is correct and I can connect to the server without any
>>>>> problem
>>>>> at all...probably because I'm an administrator on the server. The
>>>>> only
>>>>> other user who has been able to successfully connect to the server
>>>>> using the
>>>>> ADP is also an administrator on the server.
>>>>>
>>>>> If another non-administrator opens the application while it is pointed
>>>>> at
>>>>> the test server (MSDE 2000), then the user can go into File/Connection
>>>>> and
>>>>> change the server to the production server. However, if the user
>>>>> clicks on
>>>>> the "Test Connection" button, the below error displays:
>>>>>
>>>>> "Test connection failed because of an error in initializing provider.
>>>>> Login
>>>>> failed for user 'someuser'"
>>>>>
>>>>> All the clues appear to indicate a security problem, but I can't seem
>>>>> to
>>>>> find it. When I go into Enterprise Manager on the production server,
>>>>> the
>>>>> users appear in the "Users" folder for the database. When I look at a
>>>>> user's properties, the correct security roles appear to be assigned.
>>>>> When I
>>>>> display the object permissions, *none* of the boxes are checked. I'm
>>>>> not
>>>>> sure whether this is correct. Since specific object permissions are
>>>>> assigned via the roles, I don't know whether any of the boxes should
>>>>> be
>>>>> checked. If they are supposed to be checked, then why are they not
>>>>> checked?
>>>>> Is there something special I need to do to re-activate the security
>>>>> permissions?
>>>>>
>>>>> ...or am I on the wrong track and need to check something else?
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


.