Windows authentication still prompts for database login

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ron Porter - 26 Jun 2008 20:43 GMT

SQL Server 2005:
I have some Crystal Reports XI reports that prompt the user with a full database login screen (server, database, username, password). The login shows Integrated Security as turned off and the database field is blank (that is where I think the problem lies).

* The report settings seem to the same as for those reports that do work (and I did check to be sure that the report itself is configured to use integrated security)
* Other reports work fine
* The database is set to allow the Windows group that the user is a member of
* As far as I can tell, all the relevant database objects carry the same permission settings as found on objects where everything works as expected
* It doesn't fail for everybody, but close. As far as I can tell, only members of Domain Admins are successfully running these reports, but members of the other groups have no trouble with all the other reports.

Obviously, I'm missing something, but I'm at my wits end. Users claim that the broken reports were working a couple of weeks ago, so it may be that something changed, but what?

If you suspect that this is really a Crystal-specific problem, let me know and I'll dig deeper on that angle.

Reply to this Message

Uri Dimant - 29 Jun 2008 06:35 GMT

Ron
If you connect via Windows Authentication SQL Server prompts for login BUT
not for the password. Can you be more specific?

> SQL Server 2005:
> I have some Crystal Reports XI reports that prompt the user with a full
[quoted text clipped - 24 lines]
> ------= Binary Usenet downloading made easy =---------
> -= Get GrabIt for free from http://www.shemes.com/ =-

Reply to this Message

Dan Guzman - 29 Jun 2008 18:27 GMT

> * The database is set to allow the Windows group that the user is a member
> of

Check that a member account can connect the SQL Server via the group as
expected:

EXEC xp_logininfo 'MyDomain\GroupMemberUserAccount', 'all'

Check to ensure the Windows Group can access the database as expected:

EXEC sp_helplogins 'MyDomain\WindowsGroupName

If all looks well, you can test security from SSMS or Query Analyzer using
one of the scripts below:

--SQL 2000
USE MyDatabase;
GO
SETUSER 'MyDomain\GroupMemberUserAccount';
GO
SELECT USER_NAME();
GO
--test object permissions here
GO
SETUSER;
GO

--SQL 2005
USE MyDatabase;
GO
EXECUTE AS LOGIN = 'MyDomain\GroupMemberUserAccount';
GO
SELECT USER_NAME();
GO
--test object permissions here
GO
REVERT;
GO

> * It doesn't fail for everybody, but close. As far as I can tell, only
> members of Domain Admins are successfully running these reports, but
> members of the other groups have no trouble with all the other reports.

The fact that privileged users can connect and run the report without
problems probably indicates that all is well on the report side.

Since the security used to work and the problem has affects only one Windows
group now, something has likely changed on either the SQL Server or Windows
security side.

Perhaps the Windows group was recreated for some reason. In that case,
you'll need to drop and recreate the group on the SQL side too so that SQL
knows about the new Windows account SID. SQL Server uses the SID rather
than the group name for Windows security.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

Reply to this Message

Ron Porter - 03 Jul 2008 18:35 GMT

Thanks, Dan. Here are my results:

> EXEC xp_logininfo 'MyDomain\GroupMemberUserAccount', 'all'

accountname type privilege mapped login name permission path
MyDomain\GroupMemberUserAccount group user MyDomain\GroupMemberUserAccount NULL

> Check to ensure the Windows Group can access the database as expected:
>
> EXEC sp_helplogins 'MyDomain\WindowsGroupName

login name                            SID        DefDBName        DefLangName    Auser    ARemote
MyDomain\GroupMemberUserAccount    0x0....    CCLInformationStore    English            yes     no

login name                            dbName    UserName                            UserOrAlias
MyDomain\GroupMemberUserAccount    MyDBName    MyDomain\GroupMemberUserAccount    User
MyDomain\GroupMemberUserAccount    MyDBName    db_datareader                        MemberOf
MyDomain\GroupMemberUserAccount    MyDBName    db_datawriter                        MemberOf

That looks sensible to me, so I moved on, first to SQL Server 2000
on another box where we have the same problem

> If all looks well, you can test security from SSMS or Query Analyzer using
> one of the scripts below:
[quoted text clipped - 10 lines]
> SETUSER;
> GO

then to the SQL Server 2005 where the problem was first reported

> --SQL 2005
> USE MyDatabase;
> GO
> EXECUTE AS LOGIN = 'MyDomain\GroupMemberUserAccount'; -- this line reports: Msg 15406, Level 16, State 1, Line 1

Cannot execute as the server principal because the
principal "MyDomain\GroupMemberUserAccount" does not exist, this type
of principal cannot be impersonated, or you do not have permission.

> GO
> SELECT USER_NAME(); -- this line reports "dbo" (keeping in mind that the Execute As failed, so this must be the original context)
[quoted text clipped - 3 lines]
> REVERT;
> GO

NOTE: I completely removed and replaced the Domain Users account and
login on our legacy SQL Server 2000 system as a test. The results
reported above did not change, so I didn't do the same on our production
2005 server.

Why does the username get reported as dbo when I'm logged in via
Integrated Security, presumably as MyDomain\Domain Admins?

Signature

ronp

Reply to this Message

Dan Guzman - 03 Jul 2008 23:28 GMT

>> EXEC xp_logininfo 'MyDomain\GroupMemberUserAccount', 'all'
> accountname type privilege mapped login name permission path
> MyDomain\GroupMemberUserAccount group user MyDomain\GroupMemberUserAccount
> NULL

I see that the reported type is "group" and the permission path NULL. I
would expect the reported type to be "user" and the permission path to be
'MyDomain\WindowsGroupName' if the user account was specified. Did you
perhaps specify the group account name instead of the user account name as
the first xp_logininfo parameter?

>> EXEC sp_helplogins 'MyDomain\WindowsGroupName
> login name SID DefDBName DefLangName Auser ARemote
[quoted text clipped - 5 lines]
> MyDomain\GroupMemberUserAccount MyDBName db_datareader MemberOf
> MyDomain\GroupMemberUserAccount MyDBName db_datawriter MemberOf

Similarly, I would expect 'MyDomain\WindowsGroupName' to be listed under the
login name column instead of the user account name if the group account name
was specified.

It's unclear to me how the group is setup in your environment. You might
try granting database access with the examples below:

--SQL 2000
EXEC sp_grantlogin 'MyDomain\WindowsGroupName'
USE MyDBName
EXEC sp_grantdbaccess 'MyDomain\WindowsGroupName'
--add role memberships and grant object permissions here
GO

--SQL 2005
CREATE LOGIN [MyDomain\WindowsGroupName] FROM WINDOWS;
USE MyDBName
CREATE USER [MyDomain\WindowsGroupName];
--add role memberships and grant object permissions here

> Why does the username get reported as dbo when I'm logged in via
> Integrated Security, presumably as MyDomain\Domain Admins?

By default, members of the local Administratiors group (which includes
Domain Admins by default) access SQL Server via the BUILTIN\Administrators
login. This group is a sysadmin role member and is therefore mapped to the
privileged 'dbo' user in all databases.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

> Thanks, Dan. Here are my results:
>
[quoted text clipped - 64 lines]
> Why does the username get reported as dbo when I'm logged in via
> Integrated Security, presumably as MyDomain\Domain Admins?

Reply to this Message

Ron Porter - 04 Jul 2008 23:08 GMT

It would help if I could read :(

Once I changed the parameters to reference the username instead of the
group name, I got the results you were expecting. xp_logininfo shows the
type as user and the permission path as MyDomain\Domain Users.

Running CREATE LOGIN and CREATE USER gave the errors:
Msg 15025, Level 16, State 2, Line 2
The server principal 'CRESTLINECOACH\Domain Users' already exists.
Msg 15023, Level 16, State 1, Line 4
User, group, or role 'CRESTLINECOACH\Domain Users' already exists in
the current database.

I interpret that to mean that the login and user were already setup.

Interestingly enough, sp_helplogins returns nothing (0 records in both
results).

The permissions test via impersonation runs without error because
I'm now impersonating a user now instead trying to impersonate a
group. The actual test itself (select * from viewName) passes, even
though the user is still unable to run the report without getting a
login prompt. I don't know why I didn't try earlier, but I did try
using our sa login at the prompt without success.

Unless you know of a reason why an sa login would be rejected at a
user-level prompt, I'd have to guess that the report and SQL
Server are not on proper speaking terms.
--
ronp

>>> EXEC xp_logininfo 'MyDomain\GroupMemberUserAccount', 'all'
>> accountname type privilege mapped login name permission path
[quoted text clipped - 44 lines]
> login. This group is a sysadmin role member and is therefore mapped to the
> privileged 'dbo' user in all databases.

Signature

Signature file not found

Reply to this Message

Dan Guzman - 05 Jul 2008 00:32 GMT

> Interestingly enough, sp_helplogins returns nothing (0 records in both
> results).

That's odd. Was that with specifying 'MyDomain\Domain Users'?

> The permissions test via impersonation runs without error because
> I'm now impersonating a user now instead trying to impersonate a
> group. The actual test itself (select * from viewName) passes, even
> though the user is still unable to run the report without getting a
> login prompt. I don't know why I didn't try earlier, but I did try
> using our sa login at the prompt without success.

I think the sa login should work as long as the server is configured to
allow SQL authentication

One thing you might try is to connect using another application or tool when
logged in as the user. If you don't have one on the user PC, you might try
saving the VBScript below to a file with a ".vbs" extension and execute to
test connectivity. Just change the server and database name in the
connection string:

Set connection = CreateObject("ADODB.Connection")
connection.Open "Provider=SQLOLEDB" & _
";Data Source=MyServer" & _
";Initial Catalog=MyDatabase" & _
";Integrated Security=SSPI"
MsgBox "Connection ok"
connection.Execute "SELECT TOP 1 FROM dbo.MyTable"
MsgBox "Query ok"
connection.Close

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

> It would help if I could read :(
>
[quoted text clipped - 82 lines]
>> the
>> privileged 'dbo' user in all databases.

Reply to this Message

Ron Porter - 07 Jul 2008 19:06 GMT

>> Interestingly enough, sp_helplogins returns nothing (0 records in both
>> results).
>
> That's odd. Was that with specifying 'MyDomain\Domain Users'?

Yes, it was

> I think the sa login should work as long as the server is configured to
> allow SQL authentication

As it happens, I did verify that the server is configured to allow SQL
authentication and that I actually can do so using the sa account.

> One thing you might try is to connect using another application or tool when
> logged in as the user. If you don't have one on the user PC, you might try
> saving the VBScript below to a file with a ".vbs" extension and execute to
> test connectivity. Just change the server and database name in the
> connection string:

Thanks for the script. I tested first from my login to verify that I can
actually make the script work. Then I modified to hit two views
instead of one so that I could include both working
and failing views. Then I had a few different users execute the script.
It ran and reported success on all attempts.

I'm guessing that means something is wrong with the reports or their
configurations. As far as I can tell, they're set up properly, but I'll
get our report writer back on the job.

Signature

ronp

Reply to this Message

Ron Porter - 08 Jul 2008 00:02 GMT

> Thanks for the script. I tested first from my login to verify that I can
> actually make the script work. Then I modified to hit two views
[quoted text clipped - 5 lines]
> configurations. As far as I can tell, they're set up properly, but I'll
> get our report writer back on the job.

Well, we created new reports from scratch. One report against a view
we've had no trouble with and one against a view that we know is not
working. Both reports fail in exactly the same way: login prompt that
won't even get us in via the sa account.

I don't know about anybody else, but this sounds less like a
SQL Server problem and more like a Business Objects (Crystal) problem.
I'll keep monitoring this thread, but I'm also going to head over to
BO's site and see what I can track down.

Thanks!

Signature

Ron

Reply to this Message

Dan Guzman - 08 Jul 2008 02:18 GMT

> I don't know about anybody else, but this sounds less like a
> SQL Server problem and more like a Business Objects (Crystal) problem.
> I'll keep monitoring this thread, but I'm also going to head over to
> BO's site and see what I can track down.

I agree it looks like a Crystal problem since you can connect and query from
other apps using the same security credentials.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

>> Thanks for the script. I tested first from my login to verify that I can
>> actually make the script work. Then I modified to hit two views
[quoted text clipped - 17 lines]
>
> Thanks!

Reply to this Message

Ron Porter - 16 Jul 2008 16:49 GMT

>> I don't know about anybody else, but this sounds less like a
>> SQL Server problem and more like a Business Objects (Crystal) problem.
[quoted text clipped - 3 lines]
> I agree it looks like a Crystal problem since you can connect and query from
> other apps using the same security credentials.

Well, that's it! Every report modified by a particular user suffers the
same fate. We're working through fixing up the reports from a different
user/machine and will reinstall Business One on the offending machine.

Thanks, for all your help and sorry about the wild-goose chase.

Signature

-
Ron Porter
Programmer/Analyst
Crestline Coach Ltd
www.crestlinecoach.com

Reply to this Message

Dan Guzman - 17 Jul 2008 03:37 GMT

No problem, Ron. I know it's hard to troubleshoot problems like this when
the symptoms don't reflect the real problem.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

>>> I don't know about anybody else, but this sounds less like a
>>> SQL Server problem and more like a Business Objects (Crystal) problem.
[quoted text clipped - 10 lines]
>
> Thanks, for all your help and sorry about the wild-goose chase.

Reply to this Message