Logging query errors: severity

Showing posts with label severity. Show all posts

Monday, March 19, 2012

Login failed - Error Number: 18456 - Severity: 14 - State: 1

Hi.
After re-installing Windows Vista on my PC, I can no longer log on to my
local SQL Server 2005 (SP2) (even though my account name is the same for
Vista). I can not even connect with a new query, so I can not update user
information or anything like that. It gives me this error:
Login failed for user 'Thomas-PC\Thomas'. (.Net SqlClient Data Provider)
--
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
--
Server Name: thomas-pc
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536
--
Program Location:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean
enlistOK)
at
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64
timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String
host, String newPassword, Boolean redirectedUserInstance, SqlConnection
owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String newPassword,
Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo, String
newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at
Microsoft.SqlServer.Management.UI.ConnectionDlg.NewRegisteredServerForm.testConnection_Click(Object sender, EventArgs e)
Unfortunately the link provided above for more help takes me to a page that
does not exist.
I did try to uninstall the SQL Server 2005 and re-install it again but
without any difference.
I can connect to servers on other servers, but as I mentioned not my local
SQL server.
Thanks for your advise/help,
ThomasCan you connect if you launch SSMS with "run as Administrator"? If so, add
your Windows login to the sysadmin role so that you don't need to run with
elevated privileges , except when managing services.
Hope this helps.
Dan Guzman
SQL Server MVP
"Thomas Vang" <Thomas Vang@.discussions.microsoft.com> wrote in message
news:6DD3607B-2A9B-4C69-BBBD-61ADA479D211@.microsoft.com...
> Hi.
> After re-installing Windows Vista on my PC, I can no longer log on to my
> local SQL Server 2005 (SP2) (even though my account name is the same for
> Vista). I can not even connect with a new query, so I can not update user
> information or anything like that. It gives me this error:
> Login failed for user 'Thomas-PC\Thomas'. (.Net SqlClient Data Provider)
> --
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
> --
> Server Name: thomas-pc
> Error Number: 18456
> Severity: 14
> State: 1
> Line Number: 65536
> --
> Program Location:
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj)
> at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean
> enlistOK)
> at
> System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
> serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64
> timerExpire, SqlConnection owningObject)
> at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String
> host, String newPassword, Boolean redirectedUserInstance, SqlConnection
> owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
> at
> System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
> owningObject, SqlConnectionString connectionOptions, String newPassword,
> Boolean redirectedUserInstance)
> at
> System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
> identity, SqlConnectionString connectionOptions, Object providerInfo,
> String
> newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
> at
> System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
> options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
> owningConnection)
> at
> System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection
> owningConnection, DbConnectionPoolGroup poolGroup)
> at
> System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
> owningConnection)
> at
> System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
> outerConnection, DbConnectionFactory connectionFactory)
> at System.Data.SqlClient.SqlConnection.Open()
> at
> Microsoft.SqlServer.Management.UI.ConnectionDlg.NewRegisteredServerForm.testConnection_Click(Object
> sender, EventArgs e)
> Unfortunately the link provided above for more help takes me to a page
> that
> does not exist.
> I did try to uninstall the SQL Server 2005 and re-install it again but
> without any difference.
> I can connect to servers on other servers, but as I mentioned not my local
> SQL server.
> Thanks for your advise/help,
> Thomas|||Dan - You' da man !!! Thanks a lot. Yes, I could launch SSMS as
asministrator, and added my windows login and not everything's just fine.
I really appreciate your help and thank you once again.
Best regards and have a nice day,
Thomas
"Dan Guzman" wrote:
> Can you connect if you launch SSMS with "run as Administrator"? If so, add
> your Windows login to the sysadmin role so that you don't need to run with
> elevated privileges , except when managing services.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Thomas Vang" <Thomas Vang@.discussions.microsoft.com> wrote in message
> news:6DD3607B-2A9B-4C69-BBBD-61ADA479D211@.microsoft.com...
> > Hi.
> >
> > After re-installing Windows Vista on my PC, I can no longer log on to my
> > local SQL Server 2005 (SP2) (even though my account name is the same for
> > Vista). I can not even connect with a new query, so I can not update user
> > information or anything like that. It gives me this error:
> >
> > Login failed for user 'Thomas-PC\Thomas'. (.Net SqlClient Data Provider)
> >
> > --
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
> >
> > --
> > Server Name: thomas-pc
> > Error Number: 18456
> > Severity: 14
> > State: 1
> > Line Number: 65536
> >
> > --
> > Program Location:
> >
> > at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> > exception, Boolean breakConnection)
> > at
> > System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
> > stateObj)
> > at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> > SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> > bulkCopyHandler, TdsParserStateObject stateObj)
> > at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean
> > enlistOK)
> > at
> > System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
> > serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64
> > timerExpire, SqlConnection owningObject)
> > at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String
> > host, String newPassword, Boolean redirectedUserInstance, SqlConnection
> > owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
> > at
> > System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
> > owningObject, SqlConnectionString connectionOptions, String newPassword,
> > Boolean redirectedUserInstance)
> > at
> > System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
> > identity, SqlConnectionString connectionOptions, Object providerInfo,
> > String
> > newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
> > at
> > System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
> > options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
> > owningConnection)
> > at
> > System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection
> > owningConnection, DbConnectionPoolGroup poolGroup)
> > at
> > System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
> > owningConnection)
> > at
> > System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
> > outerConnection, DbConnectionFactory connectionFactory)
> > at System.Data.SqlClient.SqlConnection.Open()
> > at
> > Microsoft.SqlServer.Management.UI.ConnectionDlg.NewRegisteredServerForm.testConnection_Click(Object
> > sender, EventArgs e)
> >
> > Unfortunately the link provided above for more help takes me to a page
> > that
> > does not exist.
> > I did try to uninstall the SQL Server 2005 and re-install it again but
> > without any difference.
> > I can connect to servers on other servers, but as I mentioned not my local
> > SQL server.
> >
> > Thanks for your advise/help,
> > Thomas
>|||I'm glad I was able to help you out, Thomas. IIRC, the SP2 install can also
add logins to the sysadmin role so you don't need to do it manually.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Thomas Vang" <ThomasVang@.discussions.microsoft.com> wrote in message
news:978F3942-1196-4598-865E-A7A68295A4CD@.microsoft.com...
> Dan - You' da man !!! Thanks a lot. Yes, I could launch SSMS as
> asministrator, and added my windows login and not everything's just fine.
> I really appreciate your help and thank you once again.
> Best regards and have a nice day,
> Thomas
>
> "Dan Guzman" wrote:
>> Can you connect if you launch SSMS with "run as Administrator"? If so,
>> add
>> your Windows login to the sysadmin role so that you don't need to run
>> with
>> elevated privileges , except when managing services.
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Thomas Vang" <Thomas Vang@.discussions.microsoft.com> wrote in message
>> news:6DD3607B-2A9B-4C69-BBBD-61ADA479D211@.microsoft.com...
>> > Hi.
>> >
>> > After re-installing Windows Vista on my PC, I can no longer log on to
>> > my
>> > local SQL Server 2005 (SP2) (even though my account name is the same
>> > for
>> > Vista). I can not even connect with a new query, so I can not update
>> > user
>> > information or anything like that. It gives me this error:
>> >
>> > Login failed for user 'Thomas-PC\Thomas'. (.Net SqlClient Data
>> > Provider)
>> >
>> > --
>> > For help, click:
>> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
>> >
>> > --
>> > Server Name: thomas-pc
>> > Error Number: 18456
>> > Severity: 14
>> > State: 1
>> > Line Number: 65536
>> >
>> > --
>> > Program Location:
>> >
>> > at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
>> > exception, Boolean breakConnection)
>> > at
>> > System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
>> > stateObj)
>> > at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
>> > SqlCommand cmdHandler, SqlDataReader dataStream,
>> > BulkCopySimpleResultSet
>> > bulkCopyHandler, TdsParserStateObject stateObj)
>> > at
>> > System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean
>> > enlistOK)
>> > at
>> > System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
>> > serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64
>> > timerExpire, SqlConnection owningObject)
>> > at
>> > System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String
>> > host, String newPassword, Boolean redirectedUserInstance, SqlConnection
>> > owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
>> > at
>> > System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
>> > owningObject, SqlConnectionString connectionOptions, String
>> > newPassword,
>> > Boolean redirectedUserInstance)
>> > at
>> > System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
>> > identity, SqlConnectionString connectionOptions, Object providerInfo,
>> > String
>> > newPassword, SqlConnection owningObject, Boolean
>> > redirectedUserInstance)
>> > at
>> > System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
>> > options, Object poolGroupProviderInfo, DbConnectionPool pool,
>> > DbConnection
>> > owningConnection)
>> > at
>> > System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection
>> > owningConnection, DbConnectionPoolGroup poolGroup)
>> > at
>> > System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
>> > owningConnection)
>> > at
>> > System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
>> > outerConnection, DbConnectionFactory connectionFactory)
>> > at System.Data.SqlClient.SqlConnection.Open()
>> > at
>> > Microsoft.SqlServer.Management.UI.ConnectionDlg.NewRegisteredServerForm.testConnection_Click(Object
>> > sender, EventArgs e)
>> >
>> > Unfortunately the link provided above for more help takes me to a page
>> > that
>> > does not exist.
>> > I did try to uninstall the SQL Server 2005 and re-install it again but
>> > without any difference.
>> > I can connect to servers on other servers, but as I mentioned not my
>> > local
>> > SQL server.
>> >
>> > Thanks for your advise/help,
>> > Thomas
>>|||Thanks Dan! This helped with me running into issues when trying to restart
my SQL Server 2005 Developer Edition instance on Vista Business and failing
due to authentication issues, even though I am an administrator on my
workstation.
Laurence
"Dan Guzman" wrote:
> Can you connect if you launch SSMS with "run as Administrator"? If so, add
> your Windows login to the sysadmin role so that you don't need to run with
> elevated privileges , except when managing services.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Thomas Vang" <Thomas Vang@.discussions.microsoft.com> wrote in message
> news:6DD3607B-2A9B-4C69-BBBD-61ADA479D211@.microsoft.com...
> > Hi.
> >
> > After re-installing Windows Vista on my PC, I can no longer log on to my
> > local SQL Server 2005 (SP2) (even though my account name is the same for
> > Vista). I can not even connect with a new query, so I can not update user
> > information or anything like that. It gives me this error:
> >
> > Login failed for user 'Thomas-PC\Thomas'. (.Net SqlClient Data Provider)
> >
> > --
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
> >
> > --
> > Server Name: thomas-pc
> > Error Number: 18456
> > Severity: 14
> > State: 1
> > Line Number: 65536
> >
> > --
> > Program Location:
> >
> > at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> > exception, Boolean breakConnection)
> > at
> > System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
> > stateObj)
> > at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> > SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> > bulkCopyHandler, TdsParserStateObject stateObj)
> > at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean
> > enlistOK)
> > at
> > System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo
> > serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64
> > timerExpire, SqlConnection owningObject)
> > at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String
> > host, String newPassword, Boolean redirectedUserInstance, SqlConnection
> > owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
> > at
> > System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
> > owningObject, SqlConnectionString connectionOptions, String newPassword,
> > Boolean redirectedUserInstance)
> > at
> > System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
> > identity, SqlConnectionString connectionOptions, Object providerInfo,
> > String
> > newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
> > at
> > System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
> > options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
> > owningConnection)
> > at
> > System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection
> > owningConnection, DbConnectionPoolGroup poolGroup)
> > at
> > System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
> > owningConnection)
> > at
> > System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
> > outerConnection, DbConnectionFactory connectionFactory)
> > at System.Data.SqlClient.SqlConnection.Open()
> > at
> > Microsoft.SqlServer.Management.UI.ConnectionDlg.NewRegisteredServerForm.testConnection_Click(Object
> > sender, EventArgs e)
> >
> > Unfortunately the link provided above for more help takes me to a page
> > that
> > does not exist.
> > I did try to uninstall the SQL Server 2005 and re-install it again but
> > without any difference.
> > I can connect to servers on other servers, but as I mentioned not my local
> > SQL server.
> >
> > Thanks for your advise/help,
> > Thomas
>

Login failed - Error Number: 18456 - Severity: 14 - State: 1

Monday, March 12, 2012

Login Error: 18456, Severity: 14, State:11

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.
========================================
==============

Login error: 18456, Severity: 14, State: 11.

Hi,
we did an clean install of MOSS2007 and SQL2005. Both on different servers.
Everything worked well and suddenly we are getting this error and MOSS2007
stops working. The second message in the error log is: 'Login failed for user
'Domain\user'.
If I put the user in the SysAdmin serverrole they are able to connect.
Strange thing is that this is also happening on our SP2003/SQL2000 servers.
But on these servers it's gone after an short period.
Someone any idea?
--
Robert Hartskeerl, MCTS, MCITPHi Robert
State 11 is "Login is valid, but server access failed." my guess is that the
default database is wrong or you don't have access to it. You could try
logging in as that user and use SQLCMD or osql to change the database you
connect to. sp_defaultdb can be used to change the default database.
John
"Robert Hartskeerl" wrote:
> Hi,
> we did an clean install of MOSS2007 and SQL2005. Both on different servers.
> Everything worked well and suddenly we are getting this error and MOSS2007
> stops working. The second message in the error log is: 'Login failed for user
> 'Domain\user'.
> If I put the user in the SysAdmin serverrole they are able to connect.
> Strange thing is that this is also happening on our SP2003/SQL2000 servers.
> But on these servers it's gone after an short period.
> Someone any idea?
> --
> Robert Hartskeerl, MCTS, MCITP|||Hello, Robert
Maybe one of following will help:
http://blogs.msdn.com/sql_protocols/archive/2007/06/18/connecting-to-sql-server-2005-on-vista-and-longhorn.aspx
http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx
Razvan Socol
SQL Server MVP|||Thanks for your response John.
We already solved this with the information on connect.microsoft.com.
We had created extra tcp enpoints for different users. What happens is that
SQL Server removes the public role from TCP-Connect. So while the other
applications that used the non-default endpoints only sa's are able to
connect to the default endpoint.
After adding TCP-Endpoint to the public role we can connect.
Robert Hartskeerl
--
Robert Hartskeerl, MCTS, MCITP
"John Bell" wrote:
> Hi Robert
> State 11 is "Login is valid, but server access failed." my guess is that the
> default database is wrong or you don't have access to it. You could try
> logging in as that user and use SQLCMD or osql to change the database you
> connect to. sp_defaultdb can be used to change the default database.
> John
>
> "Robert Hartskeerl" wrote:
> > Hi,
> >
> > we did an clean install of MOSS2007 and SQL2005. Both on different servers.
> > Everything worked well and suddenly we are getting this error and MOSS2007
> > stops working. The second message in the error log is: 'Login failed for user
> > 'Domain\user'.
> >
> > If I put the user in the SysAdmin serverrole they are able to connect.
> >
> > Strange thing is that this is also happening on our SP2003/SQL2000 servers.
> > But on these servers it's gone after an short period.
> >
> > Someone any idea?
> > --
> > Robert Hartskeerl, MCTS, MCITP

Monday, February 20, 2012

Logging severity levels less than 19 ?

sp_altermessage can be usefull only when I know message number.
But what to do when I want to log all errors with severity 10, 11, 12, etc ?
Albert Lyubarsky
Comtec LTD
IsraelAlbert, shalom
[url]http://www.sqlservercentral.com/columnists/RDyess/traceflags_printversion.asp[/url
]
Lookup 2701 tracse flag
"albertly" <mynickalbert@.community.nospam> wrote in message
news:717D3800-19F9-42A4-9E53-BDB3FD373156@.microsoft.com...
> sp_altermessage can be usefull only when I know message number.
> But what to do when I want to log all errors with severity 10, 11, 12, etc
> ?
> --
> Albert Lyubarsky
> Comtec LTD
> Israel

Logging severity levels less than 19 ?

sp_altermessage can be usefull only when I know message number.
But what to do when I want to log all errors with severity 10, 11, 12, etc ?
--
Albert Lyubarsky
Comtec LTD
IsraelAlbert, shalom
http://www.sqlservercentral.com/columnists/RDyess/traceflags_printversion.asp
Lookup 2701 tracse flag
"albertly" <mynickalbert@.community.nospam> wrote in message
news:717D3800-19F9-42A4-9E53-BDB3FD373156@.microsoft.com...
> sp_altermessage can be usefull only when I know message number.
> But what to do when I want to log all errors with severity 10, 11, 12, etc
> ?
> --
> Albert Lyubarsky
> Comtec LTD
> Israel

Monday, March 19, 2012

Login failed - Error Number: 18456 - Severity: 14 - State: 1

Login failed - Error Number: 18456 - Severity: 14 - State: 1

Login failed - Error Number: 18456 - Severity: 14 - State: 1

Monday, March 12, 2012

Login Error: 18456, Severity: 14, State:11

Login error: 18456, Severity: 14, State: 11.

Monday, February 20, 2012

Logging severity levels less than 19 ?

Logging severity levels less than 19 ?

Logging query errors

Blog Archive

About Me