Incorrect Logon-type showed in the Session table?

pdj

NAV5SP1 with update 1, SQL2005SP2 with update 8
I have a single pc which always seems to login to our database using Windows Authentication. No matter if the user logs in using Windows or Database Authentication. We have tried to restart Windows. Then we tried re-installing NAV. Nothing helped. Other sessions are shown correctly, but this single pc is “sick” in some way. We have some code that acts differently based on the logontype, so we require this field to be correct. I know the session table is simply a SQL view, but how do I troubleshoot the problem?

kine

Any parameter passed to the client from the used shortcut? Did you tried to run the fin.exe/finsql.exe directly?

pdj

We have tried with "clean" zup-file and forcing NTAuthentication in the parameters but no change. ](*,)

Then someone discovered that the Net Type was different! ( \:D/ \:D/ Thank you Morten \:D/ \:D/)

It turns out that it changed default Net Type from TCP to NamedPipes once the windows user was upgraded to SQLAdmin. So by forcing it to use TCP it works like a dream...

nolabel

The real problem lies in how the session view resolves if it is windows user. It does by checking if the ntdomain field is filled in the view. This is incorrect. If you connect to the database using a trusted connection it will fill the ntdomain field evn if you login using a database login. This can be solved by changing the session view. If you run the following script from the query analyzer it will work correctly. This code works for SQL2000 and SQL2005.

USE [DATABASE_NAME]
GO
/****** Object:  View [dbo].[Session]    Script Date: 11/06/2008 13:16:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW     
[dbo].[Session] AS
SELECT
CAST(SP.[spid] AS INTEGER) AS "Connection ID",
CAST(RTRIM(SP.[loginame]) AS NVARCHAR(64)) COLLATE Latin1_General_CS_AS AS "User ID",
CAST(CASE WHEN SP.[spid] = @@SPID THEN 1 ELSE 0 END AS TINYINT) AS "My Session",
CONVERT(DATETIME, '1754-01-01 '+CONVERT(CHAR(8), 
SP.[login_time], 108), 120) AS "Login Time",
CONVERT(DATETIME, CONVERT(CHAR(10), SP.[login_time], 120)+' 00:00:00:000', 121) AS "Login Date",
CAST (SD.[name] AS NVARCHAR(128)) COLLATE Latin1_General_CS_AS AS "Database Name",
CAST (RTRIM(SP.[program_name]) AS NVARCHAR(64)) COLLATE Latin1_General_CS_AS AS "Application Name",
SL.[isntuser] AS "Login Type",
CAST(RTRIM(SP.[hostname]) AS NVARCHAR(64)) COLLATE Latin1_General_CS_AS AS "Host Name",
SP.[cpu] AS "CPU Time (ms)",CASE WHEN SP.[memusage] < 0 THEN 0 ELSE SP.[memusage]*8 END AS "Memory Usage (KB)",SP.[physical_io] AS "Physical I_O",
CAST(CASE WHEN SP.[blocked] <> 0 THEN 1 ELSE 0 END AS TINYINT) AS "Blocked",
CAST(CASE WHEN SP.[blocked] <> 0 THEN SP.[waittime] ELSE 0 END AS INTEGER) AS "Wait Time (ms)",
CAST(SP.[blocked] AS INTEGER) AS "Blocking Connection ID",
CAST(ISNULL(RTRIM(SPB.[loginame]), '') AS NVARCHAR(64)) COLLATE Latin1_General_CS_AS AS "Blocking User ID",
CAST(ISNULL(RTRIM(SPB.[hostname]), '') AS NVARCHAR(64)) COLLATE Latin1_General_CS_AS AS "Blocking Host Name",
CAST('' AS NVARCHAR(64)) COLLATE Latin1_General_CS_AS AS "Blocking Object",
CASE WHEN SP.[cmd] = 'AWAITING COMMAND' THEN CAST(DATEDIFF(SECOND, SP.[last_batch], GETDATE()) AS BIGINT)*1000 ELSE 0 END AS "Idle Time"            
FROM
[master].[dbo].[sysprocesses] AS SP
JOIN [master].[dbo].[sysdatabases] AS SD ON (SP.[dbid] = SD.[dbid])
LEFT OUTER JOIN [master].[dbo].[sysprocesses] AS SPB ON (SP.[blocked] = SPB.[spid])
LEFT JOIN [master].[dbo].[syslogins] AS SL ON (SP.[sid] = SL.[sid])
WHERE
SP.[ecid] = 0 AND 
SP.[last_batch] >= CONVERT(DATETIME, '2000-01-01 00:00:00', 120)

Hope this will help

pdj

Thanks a lot. Have you reported this to Microsoft or should I do it?