I have determined the difference between getting the above denial and succes
s
is the enabling of the 'Control server' permission on the 'Server' object
(grant = true -> you can connect) for our application. I am trying to
determine 'what' in our application at time of connect is requiring this
'Control server' permission.
I would have thought 'Connect SQL' permission would be all that is required.
The userid trying to login is a domain account, with NO admistrative
privileges, is trying to access the local (as in local to the machine, not
network access involved) SQLEXPRESS server.
The failure happens immediately trying to open the connection using connect
string
ConnectionString "Data Source=COUGAR\\SQLEXPRESS;Initial
Catalog=RC2Official;Integrated Security=True;Persist Security
Info=False;Pooling=False;Packet Size=4096" stringI have determined that installing a new instance of SQLEXPRESS, on the same
computer, and configured exactly the same as the original instance, I do not
have the problem of requiring 'Control server' permission. A simple 'Connect
Sql' permission will suffice (as I expected)
There is obviously something 'different' about my original SQLEXPRESS
database configuration, or internal tables that is causing the problem.
"Keith Dorken" wrote:
> I have determined the difference between getting the above denial and succ
ess
> is the enabling of the 'Control server' permission on the 'Server' object
> (grant = true -> you can connect) for our application. I am trying to
> determine 'what' in our application at time of connect is requiring this
> 'Control server' permission.
> I would have thought 'Connect SQL' permission would be all that is require
d.
> The userid trying to login is a domain account, with NO admistrative
> privileges, is trying to access the local (as in local to the machine, not
> network access involved) SQLEXPRESS server.
> The failure happens immediately trying to open the connection using connec
t
> string
> ConnectionString "Data Source=COUGAR\\SQLEXPRESS;Initial
> Catalog=RC2Official;Integrated Security=True;Persist Security
> Info=False;Pooling=False;Packet Size=4096" string
>|||Hi,
I understand that you could not locally connect to your SQL EXPRESS server
unless you grant "Control Server" permission to the domain login account.
If I have misunderstood, please let me know.
I think that your connection string is no problem. What is the result if
you explicitly add the database role db_datareader to the user?
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||The effect is the same. The problem is the Login is denied. The db_datareade
r
role would be the permissions on the database. The problem is the server is
denying access.
Here is the log of my test:
(This was from a cmd shell running as thoughts\LimitedUser)
C:\Program Files\Microsoft SQL Server\90\Tools\Bin>sqlcmd -S COUGAR\SQLEXPRE
SS
Msg 18456, Level 14, State 1, Server COUGAR\SQLEXPRESS, Line 1
Login failed for user 'thoughts\LimitedUser'.
C:\Program Files\Microsoft SQL Server\90\Tools\Bin>
The SQL server error log shows the lines:
05/31/2007 08:00:48,Logon,Unknown,Login failed for user
'thoughts\LimitedUser'. [CLIENT: <local machine>]
05/31/2007 08:00:48,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 1
1.
The ONLY permission that permits login is the 'Control Server' permission.
Note that I tried turning on ALL the server permissions EXCEPT 'Control
Server' and the login was still denied.
"Charles Wang[MSFT]" wrote:
> Hi,
> I understand that you could not locally connect to your SQL EXPRESS server
> unless you grant "Control Server" permission to the domain login account.
> If I have misunderstood, please let me know.
> I think that your connection string is no problem. What is the result if
> you explicitly add the database role db_datareader to the user?
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> Get notification to my posts through email? Please refer to:
> [url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif[/ur
l]
> ications
> If you are using Outlook Express, please make sure you clear the check box
> "Tools/Options/Read: Get 300 headers at a time" to see your reply promptly
.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============
>|||Keith Dorken (kadorken@.nospam.nospam) writes:
> I have determined the difference between getting the above denial and
> success is the enabling of the 'Control server' permission on the
> 'Server' object (grant = true -> you can connect) for our application. I
> am trying to determine 'what' in our application at time of connect is
> requiring this 'Control server' permission. I would have thought
> 'Connect SQL' permission would be all that is required.
> The userid trying to login is a domain account, with NO admistrative
> privileges, is trying to access the local (as in local to the machine, not
> network access involved) SQLEXPRESS server.
> The failure happens immediately trying to open the connection using
> connect string
> ConnectionString "Data Source=COUGAR\\SQLEXPRESS;Initial
> Catalog=RC2Official;Integrated Security=True;Persist Security
> Info=False;Pooling=False;Packet Size=4096" string
What happens if you try to login with SQLCMD to the Express instance
with the same login?
Does "SELECT * FROM sys.server_permissions WHERE state = 'D'" return
anything? "SELECT * FROM sys.database_permissions WHERE state = 'D'"?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,
From this article, we can see that CONTROL SERVER permission is implicitly
impact many other server permissions including CONNECT SQL. By default, if
you create a login (CONNECT SQL is granted), it is granted automatically. I
think that this permission might be denied sometime before. That is why
you must grant CONTROL SERVER permission
GRANT Server Permissions (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms186717.aspx
You can run the T-SQL statement to check the server permissions:
SELECT t2.name,t1.* FROM sys.server_permissions t1 , sys.server_principals
t2 where t1.grantee_principal_id = t2.principal_id and t1.type<>'R'
By default, once a new login created, there is only one permission CONNECT
SQL record in the result of this query.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Partner Support
PLEASE NOTE: The partner managed newsgroups are provided
to assist with break/fix issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader:
microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||"Erland Sommarskog" wrote:
> What happens if you try to login with SQLCMD to the Express instance
> with the same login?
That is how subsequent testing was done (using SQLCMD running from a cmd
shell that was operating under the username 'THOUGHTS\LimitedUser'). The
failure is the same.
> Does "SELECT * FROM sys.server_permissions WHERE state = 'D'" return
> anything? "SELECT * FROM sys.database_permissions WHERE state = 'D'"?
>
There are no entries found using the above selects. All entries have
'CONNECT SQL' with a 'G' state.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Surely you do not have to grant CONTROL SERVER permission for a Login to
access the database. CONTROL SERVER permission IMPLIES ALL the permissions t
o
the server (pretty powerful user!) The user should only require the default
CONNECT SQL permission.
Please note that I tried the case where I GRANTED ALL the permissions
IMPLIED by CONTROL SERVER, but NOT CONTROL SERVER itself and the Login was
still denied.
There is still something that CONTROL SERVER permission 'gives you' besides
implying all the other server permissions (but I don't know what 'that' is)
"Charles Wang[MSFT]" wrote:
> Hi,
> From this article, we can see that CONTROL SERVER permission is implicitly
> impact many other server permissions including CONNECT SQL. By default, if
> you create a login (CONNECT SQL is granted), it is granted automatically.
I
> think that this permission might be denied sometime before. That is why
> you must grant CONTROL SERVER permission
> GRANT Server Permissions (Transact-SQL)
> http://msdn2.microsoft.com/en-us/library/ms186717.aspx
Granting CONTROL SERVER allows the login. CONNECT SQL permission alone
should suffice (it does on the OTHER SQLEXPRESS instance running on the same
computer).
The question is why does one instance of SQLEXPRESS with (as far as I can
determine) the same configuration deny login and the other instance does not
.
Where in the registry (if any) should I look to start comparing
configuration parameters for the SQL server instances to determine if there
is some left over registry value that may be causing the problem ?
Anyplace else ? Other sys.* databases ?
> You can run the T-SQL statement to check the server permissions:
> SELECT t2.name,t1.* FROM sys.server_permissions t1 , sys.server_principal
s
> t2 where t1.grantee_principal_id = t2.principal_id and t1.type<>'R'
Here is the result of your SELECT statement:
sa 100 SERVER 0 0 1 1 COSQ CONNECT SQL G GRANT
public 100 SERVER 0 0 2 1 VWDB VIEW ANY DATABASE G GRANT
##MS_SQLResourceSigningCertificate## 100
SERVER 0 0 101 1 VWAD VIEW ANY
DEFINITION G GRANT
##MS_SQLReplicationSigningCertificate##
100 SERVER 0 0 102 1 AUTH AUTHENTICA
TE SERVER G GRANT
##MS_SQLReplicationSigningCertificate##
100 SERVER 0 0 102 1 VWAD VIEW ANY
DEFINITION G GRANT
##MS_SQLReplicationSigningCertificate##
100 SERVER 0 0 102 1 VWSS VIEW
SERVER STATE G GRANT
##MS_SQLAuthenticatorCertificate## 100 S
ERVER 0 0 103 1 AUTH AUTHENTICATE
SERVER G GRANT
BUILTIN\Administrators 100 SERVER 0 0 25
7 1 COSQ CONNECT SQL G GRANT
NT AUTHORITY\SYSTEM 100 SERVER 0 0 258 1 CO
SQ CONNECT SQL G GRANT
COUGAR\SQLServer2005MSSQLUser$COUGAR$SQL
EXPRESS 100 SERVER 0 0 259 1 COSQ CO
NNECT SQL G GRANT
BUILTIN\Users 100 SERVER 0 0 260 1 COSQ
CONNECT SQL G GRANT
COUGAR\SQLServer2005MSFTEUser$COUGAR$SQL
EXPRESS 100 SERVER 0 0 265 1 COSQ CO
NNECT SQL G GRANT
##MS_AgentSigningCertificate## 100 SERVE
R 0 0 269 1 COSQ CONNECT SQL G GRANT
COUGAR\logouser1 100 SERVER 0 0 271 1 CO
SQ CONNECT SQL G GRANT
THOUGHTS\limiteduser 100 SERVER 0 0 276
1 COSQ CONNECT SQL G GRANT
> By default, once a new login created, there is only one permission CONNECT
> SQL record in the result of this query.
And that is the only permission I want for this user. But they cannot connec
t.|||Keith Dorken (kadorken@.nospam.nospam) writes:
> "Erland Sommarskog" wrote:
> That is how subsequent testing was done (using SQLCMD running from a cmd
> shell that was operating under the username 'THOUGHTS\LimitedUser'). The
> failure is the same.
And you tried to connect to the same database, or did you also try one
like tempdb?
What happens if you create an SQL login and try with that one.
Have you checked that there are no logon triggers on the server?
Sorry, I don't really have any good ideas, and I am just clutching at
straws.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Lines: 49
X-Tomcat-ID: 91232475
MIME-Version: 1.0
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Organization: Microsoft
X-Tomcat-NG: microsoft.public.sqlserver.security
NNTP-Posting-Host: TOMCATIMPORT1 10.201.218.122
Xref: leafnode.mcse.ms microsoft.public.sqlserver.security:3486
Hi Kadorken,
Thanks for your response.
I am consulting the product team on this issue. I will let you know the
result as soon as possible.
Since the process may need a long time, I also recommend that you give
Microsoft feedback via the following link:
http://connect.microsoft.com/sql
(please logon first before you submit a feedback.)
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment