July 4, 2011 at 2:10 am
Hi,
One of our server is in mixed authentiacation mode. We can login to SQL server using the SQL logins through SSMS. Even we can connect throgh ODBC also using the SQL authentication successfully.
But user is not able to connect to the database when using SQL authentication through the application. I can see the below log in eventvwr.
Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.
Where SQL server is configured for mixed authentication. While connection is established using windows authentication through ODBC, user face no issue. Any idea on this?
Ryan
//All our dreams can come true, if we have the courage to pursue them//
July 4, 2011 at 2:47 am
Is this the only instance on that server ?
Double check you and your user are connecting to the same server\instance !
Maybe this failed connection tracker helper can help you sort it out.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 4, 2011 at 3:00 am
I checked with the user and they are connecting the right server and instance.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
July 4, 2011 at 3:14 am
you are connecting as on SSMS
ServerName
or
ServerName\NamedInstance,port
you have to check SSMS from the client PC to Server,You are creating ODBC on the application server,Is this web application or desktop ?
Is Firwall enabled on the Server ?
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
July 4, 2011 at 3:21 am
In that case double check there aren't any sql client aliasses defined that revert the connection ...
- Can you post @@version info of the instance ?
- On your instance, can you double check the setting using :
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode'
Please post the result.
-AFAIK this setting acts instance wide, so for everyone or for nobody. :ermm:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 4, 2011 at 5:39 am
Hi,
Please see the result below.
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
LoginMode2
Ryan
//All our dreams can come true, if we have the courage to pursue them//
July 4, 2011 at 6:33 am
The message doesn’t seem to be generated from SQL Server. It has no error number and also the message itself doesn’t exist in sys.messages table. Could it be that the message is raised by the application and written to the log?
By default SQL Server is configured to log failed login error in SQL Server’s error log. Can you find the entry about the failed login? If you can you should check the error’s state. According to the state you can figure out what went wrong. This URL http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx%5B/url%5D specifies what each state means.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 4, 2011 at 7:00 am
Adi Cohn-120898 (7/4/2011)
...By default SQL Server is configured to log failed login error in SQL Server’s error log. ...
I don't think it is logged in th errorlog by default - however it is a best practise -, but you will find it in the Default trace, if the instance admin kept it active.
/*
* Import Default Trace and select failed logins.
*/
/*
* read SQL default trace file name
*/
SET nocount ON
declare @wrkTraceFilename nvarchar(500)
-- read trace info
SELECT @wrkTraceFilename = convert(nvarchar(500), [value])
FROM fn_trace_getinfo(1) -- 1 = default trace
where [property] = 2
-- Property
-- 1 = Trace options. For more information, see @options in sp_trace_create (Transact-SQL).
-- 2 = File name
-- 3 = Max size
-- 4 = Stop time
-- 5 = Current trace status
print @wrkTraceFilename
if object_id('tempdb..#tmpTrace') is not null
begin
drop table #tmpTrace
end
select *
-- used this to be sure I have ALL available columns ( never mind edition, servicepack, CU, HF, ...)
into #tmpTrace
from fn_trace_gettable(@wrkTraceFilename, default)
;
/* Show 'Audit Login Failed' trace data */
Select TE.name as EventName
, db_name(T.DatabaseID) as DatabaseName0
, T.HostName as TheHostName
, T.ApplicationName as TheApplicationName
, T.LoginName as TheLoginName
, T.StartTime as TheStartTime
, T.EndTime as TheEndTime
, Object_schema_name(T.ObjectID, T.DatabaseID) as TheObjectSchema
, Object_name(T.ObjectID, T.DatabaseID) as TheObjectName
, T.ServerName as TheServername
, T.*
from #tmpTrace T
inner join sys.trace_events TE
on TE.trace_event_id = T.EventClass
where TE.name = 'Audit Login Failed'
order by TheStartTime
, DatabaseName
, LoginName
, HostName
, ApplicationName ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 4, 2011 at 7:10 am
@adi Cohn-120898
I have checked the SQL error log and it says "Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: xx.xx.xx.xxx]"
@syed
I cannot access SQL server from other server. This is may be firewall preventing to connect.
Can it be due to accessing SQL server from untrusted domain?
Ryan
//All our dreams can come true, if we have the courage to pursue them//
July 4, 2011 at 7:43 am
Sumanta Roy (7/4/2011)
Hi,Please see the result below.
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
LoginMode2
You've posted in a SQL2005 forum !
Please pay attention the next time you launch a thread.
Advise may defer depending on the engine and edition.
Are you using the same SQLUser as the one used for the remote connection ( is there a login trigger active ? )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 4, 2011 at 8:02 am
Yes..I should post it in SQL server 2008 thread.
Are you using the same SQLUser as the one used for the remote connection ( is there a login trigger active ? )
Yes, I am using the same and there is no logon trigger.
When connecting from the application, the user is getting the below error message attached.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
July 5, 2011 at 12:13 am
Since it states user '' IMO that means someone didn't provide a SQLUser !
Check that applications parameters to configure the connection string.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 5, 2011 at 12:54 am
Is there any other port instead of default port 1433,is this your port enabled in SQL Server Database Server,first try the connection with SSMS remotely then go to application
Try to establish connection with telnet or check the port with netstat -a is your port is open or not command on cmd
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply