Authentication Mode ??

Question

Post reply

Authentication Mode ??

Ford Fairlane

SSCertifiable

Points: 7664
More actions
October 14, 2013 at 12:42 am

#299182

I have a sql server 2008 r2 install with Windows Authentication mode selected.
However I am seeing SQL accounts connect - how is this possible ??
Thanks
Hope this helps...
Ford Fairlane
Rock and Roll Detective
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

Just to verify your settings and active connections; can you post the results of the query below?

select

case when serverproperty('IsIntegratedSecurityOnly') = 0 then 'SQL' else 'Windows' end as security_setting

, sum(case when coalesce(nt_username, '') <> '' then 1 else 0 end) as nr_of_windows_logins

, sum(case when coalesce(nt_username, '') = '' then 1 else 0 end) as nr_of_sql_logins

, count(*) as total_logins#

from master..sysprocesses

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 2

security_settingnr_of_windows_loginsnr_of_sql_loginstotal_logins#

Windows 47 24 71

Hope this helps...

Ford Fairlane
Rock and Roll Detective

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

You might want to put this on the end of that query:

where hostprocess<>''

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Are they all 'sa'? Are they all low session_id? If you query sys.dm_exec_sessions, what do they show for 'IsUserProcess'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 5

None of them are 'sa', they are all a sql account 'red' using an odbc connection.

here is one line from the select

session_idlogin_timehost_nameprogram_namehost_process_idclient_versionclient_interface_namesecurity_idlogin_nament_domainnt_user_namestatuscontext_infocpu_timememory_usagetotal_scheduled_timetotal_elapsed_timeendpoint_idlast_request_start_timelast_request_end_timereadswriteslogical_readsis_user_processtext_sizelanguagedate_formatdate_firstquoted_identifierarithabortansi_null_dflt_onansi_defaultsansi_warningsansi_paddingansi_nullsconcat_null_yields_nulltransaction_isolation_levellock_timeoutdeadlock_priorityrow_countprev_errororiginal_security_idoriginal_login_namelast_successful_logonlast_unsuccessful_logonunsuccessful_logonsgroup_id

10210/15/2013 8:25:37 AMxxxxSAPDCI01R3B12 [1]unc rd ODBC36606ODBC0x2D220E9B08C8284CB972665E2AC166CDredsleeping0x16399210/15/2013 8:25:46 AM10/15/2013 8:25:46 AM004681-1us_englishmdy7101011111-10000x010500000000000515000000CC98BBF9DA303149DE4CA1FFA2060000xxxx\SAPServiceRED2

Apologies for the mess - not sure how would be the best way to represent this information here.

Hope this helps...

Ford Fairlane
Rock and Roll Detective

HanShi SSC-Dedicated Points: 33507 More actions · Answer 6

If I interpred your output correctly the value in column "original login" is xxxx\SAPServiceRED. This looks like a Windows account/AD-group to me. Can you check the value Red in column "login_name" is a really a login and not a credential?

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 7

the 'red' is a sql account.

Hope this helps...

Ford Fairlane
Rock and Roll Detective