Re: Permissions problem on SBS 2003 R2 for SQL Server 2005 clients

Mixed mode authentication is there for a reason. Some applications may be
running on a Linux box running a java/PHP application on Apache which would
probably use SQL Server. Windows authentication is not an option for this
setup. Microsoft strongly recommends Windows authentication for a purely
Microsoft-only setup (and I do, too). Now, I wouldn't recommend granting
users sysadmin privileges unless you are inviting trouble. This is something
I DO NOT DO on SQL Server systems unless necessary. I've had clients (and
these are enterprise clients, not SMEs) where an ordinary user (and even a
developer) accidentally delete tables inside a SQL Server database because
they have sysadmin privileges. Disaster recovery should have been for actual
disasters and not for human errors caused by giving users more than
necessary privileges. Having said all of these, I agree with you that
security is no place for workarounds and you have to understand that SQL
Server 2005 has been totally redesigned to keep exposed surface area as
minimum as possible. This is also the reason why you need to understand the
security mechanism of SQL Server 2005.

bass_player
MCP MCDBA MCAD MCSD MCT MCTS MCITP:DBA

"John Hackert" <hackertjohnb@xxxxxxxxx> wrote in message
news:1181606497.612465.107780@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Thank you so much for replying -

I believe the basic surface area configuration is set up properly -
once again, in SSMSE (even though the green light is off), all
elements of the database are accessible. As a test, data can be
directly changed in tables, for example, and stored procedures can be
successfully executed. I'm wondering, though, if specific port
settings need to be tweaked to make the green light turn on. BTW,
temporarily turning off client firewalls doesn't change the
phenomenon. The ADP, it seems to me, is no more than a gauge to the
effective permissions.

I'm very indebted to these newsgroups for answering some key
questions, often of the sort that demands the experience of the
"gurus" that are willing to offer their insight. I've also placed a
few posts that have remained thoroughly unanswered. While it makes
you wonder at first if you've been blacklisted (I've made enough
negative comments to qualify, I'm sure), perhaps it's more that the
concept in question is simply not in use among the professionals.
After thanking you, I wouldn't want to cause offense, but I'm
surprised that mixed mode security is even entertained. I'm "self-
taught" on everything I do - from my reading Microsoft strongly
recommends Windows-based security on SQL Server...but is it possible
that it actually doesn't work and that no one uses it? That's why I
think a Microsoft monitor should respond to an inquiry like this.
I've seen Microsoft employees respond to user questions that are more
basic than this, if it could seem too trivial (albeit usually within
newsgroups that relate to new software releases).

As I've continued to play around with the security settings on the
test system, I have found that I can provide sufficient privileges to
a client-user for the ADP itself to work by setting the SQL server
role to sysadmin, while maintaining a SBS non-administrator group of
"User," for example, even though, of course, there's still no green
light in that case. The problem would seem to be a permissions issue
between the OS and SQL server.

As I prepare for the actual implementation, I've hired a professional
IT firm for the install and maintenance who I expect can complete the
troubleshooting for me. If not, perhaps it's an appropriate use for
Microsoft paid support. It seems to me that security is no place for
workarounds.



.

Relevant Pages
Loading