Hello,
I'm having the same issur, Unicenter Service Desk can't access in to SQL Server.
This event log in Windows Event log:
Login failed for user 'ServiceDesk'. [CLIENT: 10.78.78.90]
Login failed for user 'servicedesksrv\ServiceDesk'. [CLIENT: 10.78.78.90]
For informaiton, SDM and SQL are installed in the same server
You will find after SQL capture logs:
<Events>
<Event id="65534" name="Trace Start">
<Column id="14" name="StartTime">2017-03-03T08:54:45.353-08:00</Column>
</Event>
<Event id="17" name="ExistingConnection">
<Column id="1" name="TextData">-- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
</Column>
<Column id="9" name="ClientProcessID">3464</Column>
<Column id="11" name="LoginName">SERVICEDESKSRV\servicedesk</Column>
<Column id="6" name="NTUserName">servicedesk</Column>
<Column id="10" name="ApplicationName">Microsoft SQL Server Management Studio</Column>
<Column id="12" name="SPID">51</Column>
<Column id="14" name="StartTime">2017-03-03T08:51:55.733-08:00</Column>
</Event>
<Event id="14" name="Audit Login">
<Column id="1" name="TextData">-- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
</Column>
<Column id="9" name="ClientProcessID">2792</Column>
<Column id="11" name="LoginName">SERVICEDESKSRV\servicedesk</Column>
<Column id="6" name="NTUserName">servicedesk</Column>
<Column id="10" name="ApplicationName">OSQL-32</Column>
<Column id="12" name="SPID">52</Column>
<Column id="14" name="StartTime">2017-03-03T08:55:53.103-08:00</Column>
</Event>
<Event id="13" name="SQL:BatchStarting">
<Column id="1" name="TextData"> set quoted_identifier off </Column>
<Column id="9" name="ClientProcessID">2792</Column>
<Column id="11" name="LoginName">SERVICEDESKSRV\servicedesk</Column>
<Column id="6" name="NTUserName">servicedesk</Column>
<Column id="10" name="ApplicationName">OSQL-32</Column>
<Column id="12" name="SPID">52</Column>
<Column id="14" name="StartTime">2017-03-03T08:55:53.103-08:00</Column>
</Event>
<Event id="12" name="SQL:BatchCompleted">
<Column id="11" name="LoginName">SERVICEDESKSRV\servicedesk</Column>
<Column id="15" name="EndTime">2017-03-03T08:55:53.103-08:00</Column>
<Column id="6" name="NTUserName">servicedesk</Column>
<Column id="10" name="ApplicationName">OSQL-32</Column>
<Column id="12" name="SPID">52</Column>
<Column id="14" name="StartTime">2017-03-03T08:55:53.103-08:00</Column>
<Column id="16" name="Reads">0</Column>
<Column id="18" name="CPU">0</Column>
<Column id="1" name="TextData"> set quoted_identifier off </Column>
<Column id="9" name="ClientProcessID">2792</Column>
<Column id="13" name="Duration">148</Column>
<Column id="17" name="Writes">0</Column>
</Event>
<Event id="13" name="SQL:BatchStarting">
<Column id="1" name="TextData">-------------------------------------------------------------------------------
-- Copyright (C) 2005 Computer Associates International, Inc
-- as an unpublished work. This notice does not imply unrestricted or public
-- access to these materials which are a trade secret of Computer Associates
-- International or its subsidiaries or affiliates (together referred to as
-- CA), and which may not be reproduced, used, sold or
-- transferred to any third party without CA's prior written consent.
--
-- All Rights Reserved.
--
-- RESTRICTED RIGHTS LEGEND
-- Use, duplication, or disclosure by the Government is subject to
-- restrictions as set forth in subparagraph (c)(1)(ii) of the Rights in
-- Technical Data and Computer Software clause at DFARS 252.227-7013.
-------------------------------------------------------------------------------
-- Verifies user has needed authorities
--
-- Returns 0 - OK
-- 1 - One or more errors occurred during grants
-------------------------------------------------------------------------------
-- $Header: /base/source/java/configui/resources/LOC/en-US/.RCS/sql_check_user.sql,v 1.2.1.2 2005/12/14 23:41:34 chadu01 Exp $
-------------------------------------------------------------------------------
use master
SET NOCOUNT ON
</Column>
<Column id="9" name="ClientProcessID">2792</Column>
<Column id="11" name="LoginName">SERVICEDESKSRV\servicedesk</Column>
<Column id="6" name="NTUserName">servicedesk</Column>
<Column id="10" name="ApplicationName">OSQL-32</Column>
<Column id="12" name="SPID">52</Column>
<Column id="14" name="StartTime">2017-03-03T08:55:53.103-08:00</Column>
</Event>
<Event id="12" name="SQL:BatchCompleted">
<Column id="11" name="LoginName">SERVICEDESKSRV\servicedesk</Column>
<Column id="15" name="EndTime">2017-03-03T08:55:53.103-08:00</Column>
<Column id="6" name="NTUserName">servicedesk</Column>
<Column id="10" name="ApplicationName">OSQL-32</Column>
<Column id="12" name="SPID">52</Column>
<Column id="14" name="StartTime">2017-03-03T08:55:53.103-08:00</Column>
<Column id="16" name="Reads">0</Column>
<Column id="18" name="CPU">0</Column>
<Column id="1" name="TextData">-------------------------------------------------------------------------------
-- Copyright (C) 2005 Computer Associates International, Inc
-- as an unpublished work. This notice does not imply unrestricted or public
-- access to these materials which are a trade secret of Computer Associates
-- International or its subsidiaries or affiliates (together referred to as
-- CA), and which may not be reproduced, used, sold or
-- transferred to any third party without CA's prior written consent.
--
-- All Rights Reserved.
--
-- RESTRICTED RIGHTS LEGEND
-- Use, duplication, or disclosure by the Government is subject to
-- restrictions as set forth in subparagraph (c)(1)(ii) of the Rights in
-- Technical Data and Computer Software clause at DFARS 252.227-7013.
-------------------------------------------------------------------------------
-- Verifies user has needed authorities
--
-- Returns 0 - OK
-- 1 - One or more errors occurred during grants
-------------------------------------------------------------------------------
-- $Header: /base/source/java/configui/resources/LOC/en-US/.RCS/sql_check_user.sql,v 1.2.1.2 2005/12/14 23:41:34 chadu01 Exp $
-------------------------------------------------------------------------------
use master
SET NOCOUNT ON
</Column>
<Column id="9" name="ClientProcessID">2792</Column>
<Column id="13" name="Duration">128</Column>
<Column id="17" name="Writes">0</Column>
</Event>
<Event id="13" name="SQL:BatchStarting">
<Column id="1" name="TextData">
declare @retcd int
set @retcd = 1
-------------------------------------------------------------------------------
-- Dump out version information for Support purposes
-------------------------------------------------------------------------------
select @@version
-------------------------------------------------------------------------------
-- First check to see if in sysadmin group
-------------------------------------------------------------------------------
IF IS_SRVROLEMEMBER ('sysadmin') = 1
begin
set @retcd = 0
print 'Current user is a member of the sysadmin role'
end
ELSE IF IS_MEMBER ('db_owner') = 1
begin
set @retcd = 0
print 'Current user is a member of the db_owner role'
end
-- It is OK to not replace this REPLACE because it is unlikely that
-- anyone will have a role called No_role_check
ELSE IF IS_MEMBER ('No_role_check') = 1
begin
set @retcd = 0
print 'Current user is a member of the No_role_check role'
end
ELSE
begin
set @retcd = 1
print 'Current user cannot create a database'
end
-------------------------------------------------------------------------------
-- return an error code
-------------------------------------------------------------------------------
SELECT @retcd
</Column>
<Column id="9" name="ClientProcessID">2792</Column>
<Column id="11" name="LoginName">SERVICEDESKSRV\servicedesk</Column>
<Column id="6" name="NTUserName">servicedesk</Column>
<Column id="10" name="ApplicationName">OSQL-32</Column>
<Column id="12" name="SPID">52</Column>
<Column id="14" name="StartTime">2017-03-03T08:55:53.12-08:00</Column>
</Event>
<Event id="12" name="SQL:BatchCompleted">
<Column id="11" name="LoginName">SERVICEDESKSRV\servicedesk</Column>
<Column id="15" name="EndTime">2017-03-03T08:55:53.12-08:00</Column>
<Column id="6" name="NTUserName">servicedesk</Column>
<Column id="10" name="ApplicationName">OSQL-32</Column>
<Column id="12" name="SPID">52</Column>
<Column id="14" name="StartTime">2017-03-03T08:55:53.12-08:00</Column>
<Column id="16" name="Reads">0</Column>
<Column id="18" name="CPU">0</Column>
<Column id="1" name="TextData">
declare @retcd int
set @retcd = 1
-------------------------------------------------------------------------------
-- Dump out version information for Support purposes
-------------------------------------------------------------------------------
select @@version
-------------------------------------------------------------------------------
-- First check to see if in sysadmin group
-------------------------------------------------------------------------------
IF IS_SRVROLEMEMBER ('sysadmin') = 1
begin
set @retcd = 0
print 'Current user is a member of the sysadmin role'
end
ELSE IF IS_MEMBER ('db_owner') = 1
begin
set @retcd = 0
print 'Current user is a member of the db_owner role'
end
-- It is OK to not replace this REPLACE because it is unlikely that
-- anyone will have a role called No_role_check
ELSE IF IS_MEMBER ('No_role_check') = 1
begin
set @retcd = 0
print 'Current user is a member of the No_role_check role'
end
ELSE
begin
set @retcd = 1
print 'Current user cannot create a database'
end
-------------------------------------------------------------------------------
-- return an error code
-------------------------------------------------------------------------------
SELECT @retcd
</Column>
<Column id="9" name="ClientProcessID">2792</Column>
<Column id="13" name="Duration">1073</Column>
<Column id="17" name="Writes">0</Column>
</Event>
<Event id="15" name="Audit Logout">
<Column id="11" name="LoginName">SERVICEDESKSRV\servicedesk</Column>
<Column id="15" name="EndTime">2017-03-03T08:55:53.133-08:00</Column>
<Column id="6" name="NTUserName">servicedesk</Column>
<Column id="10" name="ApplicationName">OSQL-32</Column>
<Column id="12" name="SPID">52</Column>
<Column id="14" name="StartTime">2017-03-03T08:55:53.103-08:00</Column>
<Column id="16" name="Reads">0</Column>
<Column id="18" name="CPU">0</Column>
<Column id="9" name="ClientProcessID">2792</Column>
<Column id="13" name="Duration">30000</Column>
<Column id="17" name="Writes">0</Column>
</Event>
<Event id="13" name="SQL:BatchStarting">
<Column id="1" name="TextData">SELECT
'Server[@Name=' + quotename(CAST(serverproperty(N'Servername') AS sysname),'''') + ']' + '/Login[@Name=' + quotename(log.name,'''') + ']' AS [Urn],
log.name AS [Name],
CASE WHEN N'U' = log.type THEN 0 WHEN N'G' = log.type THEN 1 WHEN N'S' = log.type THEN 2 WHEN N'C' = log.type THEN 3 WHEN N'K' = log.type THEN 4 END AS [LoginType],
log.is_disabled AS [IsDisabled],
log.create_date AS [CreateDate]
FROM
sys.server_principals AS log
WHERE
(log.type in ('U', 'G', 'S', 'C', 'K') AND log.principal_id not between 101 and 255 AND log.name <> N'##MS_AgentSigningCertificate##')
ORDER BY
[Name] ASC</Column>
<Column id="9" name="ClientProcessID">3464</Column>
<Column id="11" name="LoginName">SERVICEDESKSRV\servicedesk</Column>
<Column id="6" name="NTUserName">servicedesk</Column>
<Column id="10" name="ApplicationName">Microsoft SQL Server Management Studio</Column>
<Column id="12" name="SPID">51</Column>
<Column id="14" name="StartTime">2017-03-03T08:56:39.92-08:00</Column>
</Event>
<Event id="12" name="SQL:BatchCompleted">
<Column id="11" name="LoginName">SERVICEDESKSRV\servicedesk</Column>
<Column id="15" name="EndTime">2017-03-03T08:56:39.933-08:00</Column>
<Column id="6" name="NTUserName">servicedesk</Column>
<Column id="10" name="ApplicationName">Microsoft SQL Server Management Studio</Column>
<Column id="12" name="SPID">51</Column>
<Column id="14" name="StartTime">2017-03-03T08:56:39.92-08:00</Column>
<Column id="16" name="Reads">45</Column>
<Column id="18" name="CPU">15</Column>
<Column id="1" name="TextData">SELECT
'Server[@Name=' + quotename(CAST(serverproperty(N'Servername') AS sysname),'''') + ']' + '/Login[@Name=' + quotename(log.name,'''') + ']' AS [Urn],
log.name AS [Name],
CASE WHEN N'U' = log.type THEN 0 WHEN N'G' = log.type THEN 1 WHEN N'S' = log.type THEN 2 WHEN N'C' = log.type THEN 3 WHEN N'K' = log.type THEN 4 END AS [LoginType],
log.is_disabled AS [IsDisabled],
log.create_date AS [CreateDate]
FROM
sys.server_principals AS log
WHERE
(log.type in ('U', 'G', 'S', 'C', 'K') AND log.principal_id not between 101 and 255 AND log.name <> N'##MS_AgentSigningCertificate##')
ORDER BY
[Name] ASC</Column>
<Column id="9" name="ClientProcessID">3464</Column>
<Column id="13" name="Duration">11759</Column>
<Column id="17" name="Writes">0</Column>
</Event>
<Event id="13" name="SQL:BatchStarting">
<Column id="1" name="TextData">use [master]</Column>
<Column id="9" name="ClientProcessID">3464</Column>
<Column id="11" name="LoginName">SERVICEDESKSRV\servicedesk</Column>
<Column id="6" name="NTUserName">servicedesk</Column>
<Column id="10" name="ApplicationName">Microsoft SQL Server Management Studio</Column>
<Column id="12" name="SPID">51</Column>
<Column id="14" name="StartTime">2017-03-03T08:56:39.933-08:00</Column>
</Event>
<Event id="12" name="SQL:BatchCompleted">
<Column id="11" name="LoginName">SERVICEDESKSRV\servicedesk</Column>
<Column id="15" name="EndTime">2017-03-03T08:56:39.933-08:00</Column>
<Column id="6" name="NTUserName">servicedesk</Column>
<Column id="10" name="ApplicationName">Microsoft SQL Server Management Studio</Column>
<Column id="12" name="SPID">51</Column>
<Column id="14" name="StartTime">2017-03-03T08:56:39.933-08:00</Column>
<Column id="16" name="Reads">0</Column>
<Column id="18" name="CPU">0</Column>
<Column id="1" name="TextData">use [master]</Column>
<Column id="9" name="ClientProcessID">3464</Column>
<Column id="13" name="Duration">217</Column>
<Column id="17" name="Writes">0</Column>
</Event>
<Event id="14" name="Audit Login">
<Column id="1" name="TextData">-- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
</Column>
<Column id="9" name="ClientProcessID">3464</Column>
<Column id="11" name="LoginName">SERVICEDESKSRV\servicedesk</Column>
<Column id="6" name="NTUserName">servicedesk</Column>
<Column id="10" name="ApplicationName">Microsoft SQL Server Management Studio</Column>
<Column id="12" name="SPID">52</Column>
<Column id="14" name="StartTime">2017-03-03T08:56:41.527-08:00</Column>
</Event>
<Event id="13" name="SQL:BatchStarting">
<Column id="1" name="TextData">SET LOCK_TIMEOUT 10000</Column>
<Column id="9" name="ClientProcessID">3464</Column>
<Column id="11" name="LoginName">SERVICEDESKSRV\servicedesk</Column>
<Column id="6" name="NTUserName">servicedesk</Column>
<Column id="10" name="ApplicationName">Microsoft SQL Server Management Studio</Column>
<Column id="12" name="SPID">52</Column>
<Column id="14" name="StartTime">2017-03-03T08:56:41.527-08:00</Column>
</Event>
<Event id="12" name="SQL:BatchCompleted">
<Column id="11" name="LoginName">SERVICEDESKSRV\servicedesk</Column>
<Column id="15" name="EndTime">2017-03-03T08:56:41.527-08:00</Column>
<Column id="6" name="NTUserName">servicedesk</Column>
<Column id="10" name="ApplicationName">Microsoft SQL Server Management Studio</Column>
<Column id="12" name="SPID">52</Column>
<Column id="14" name="StartTime">2017-03-03T08:56:41.527-08:00</Column>
<Column id="16" name="Reads">0</Column>
<Column id="18" name="CPU">0</Column>
<Column id="1" name="TextData">SET LOCK_TIMEOUT 10000</Column>
<Column id="9" name="ClientProcessID">3464</Column>
<Column id="13" name="Duration">75</Column>
<Column id="17" name="Writes">0</Column>
</Event>
<Event id="13" name="SQL:BatchStarting">
<Column id="1" name="TextData">SELECT
log.name AS [Name]
FROM
sys.server_principals AS log
WHERE
(log.type in ('U', 'G', 'S', 'C', 'K') AND log.principal_id not between 101 and 255 AND log.name <> N'##MS_AgentSigningCertificate##')and(log.name=N'SERVICEDESKSRV\servicedesk')</Column>
<Column id="9" name="ClientProcessID">3464</Column>
<Column id="11" name="LoginName">SERVICEDESKSRV\servicedesk</Column>
<Column id="6" name="NTUserName">servicedesk</Column>
<Column id="10" name="ApplicationName">Microsoft SQL Server Management Studio</Column>
<Column id="12" name="SPID">52</Column>
<Column id="14" name="StartTime">2017-03-03T08:56:41.527-08:00</Column>
</Event>
<Event id="12" name="SQL:BatchCompleted">
<Column id="11" name="LoginName">SERVICEDESKSRV\servicedesk</Column>
<Column id="15" name="EndTime">2017-03-03T08:56:41.527-08:00</Column>
<Column id="6" name="NTUserName">servicedesk</Column>
<Column id="10" name="ApplicationName">Microsoft SQL Server Management Studio</Column>
<Column id="12" name="SPID">52</Column>
<Column id="14" name="StartTime">2017-03-03T08:56:41.527-08:00</Column>
<Column id="16" name="Reads">10</Column>
<Column id="18" name="CPU">0</Column>
<Column id="1" name="TextData">SELECT
log.name AS [Name]
FROM
sys.server_principals AS log
WHERE
(log.type in ('U', 'G', 'S', 'C', 'K') AND log.principal_id not between 101 and 255 AND log.name <> N'##MS_AgentSigningCertificate##')and(log.name=N'SERVICEDESKSRV\servicedesk')</Column>
<Column id="9" name="ClientProcessID">3464</Column>
<Column id="13" name="Duration">5969</Column>
<Column id="17" name="Writes">0</Column>
</Event>
<Event id="13" name="SQL:BatchStarting">
<Column id="1" name="TextData">SELECT
dtb.collation_name AS [Collation],
dtb.name AS [DatabaseName2]
FROM
master.sys.databases AS dtb
WHERE
(dtb.name=N'master')</Column>
<Column id="9" name="ClientProcessID">3464</Column>
<Column id="11" name="LoginName">SERVICEDESKSRV\servicedesk</Column>
<Column id="6" name="NTUserName">servicedesk</Column>
<Column id="10" name="ApplicationName">Microsoft SQL Server Management Studio</Column>
<Column id="12" name="SPID">52</Column>
<Column id="14" name="StartTime">2017-03-03T08:56:41.54-08:00</Column>
</Event>
<Event id="12" name="SQL:BatchCompleted">
<Column id="11" name="LoginName">SERVICEDESKSRV\servicedesk</Column>
<Column id="15" name="EndTime">2017-03-03T08:56:41.557-08:00</Column>
<Column id="6" name="NTUserName">servicedesk</Column>
<Column id="10" name="ApplicationName">Microsoft SQL Server Management Studio</Column>
<Column id="12" name="SPID">52</Column>
<Column id="14" name="StartTime">2017-03-03T08:56:41.54-08:00</Column>
<Column id="16" name="Reads">9</Column>
<Column id="18" name="CPU">16</Column>
<Column id="1" name="TextData">SELECT
dtb.collation_name AS [Collation],
dtb.name AS [DatabaseName2]
FROM
master.sys.databases AS dtb
WHERE
(dtb.name=N'master')</Column>
<Column id="9" name="ClientProcessID">3464</Column>
<Column id="13" name="Duration">12220</Column>
<Column id="17" name="Writes">0</Column>
</Event>
<Event id="13" name="SQL:BatchStarting">
<Column id="1" name="TextData">select SERVERPROPERTY(N'servername')</Column>
<Column id="9" name="ClientProcessID">3464</Column>
<Column id="11" name="LoginName">SERVICEDESKSRV\servicedesk</Column>
<Column id="6" name="NTUserName">servicedesk</Column>
<Column id="10" name="ApplicationName">Microsoft SQL Server Management Studio</Column>
<Column id="12" name="SPID">52</Column>
<Column id="14" name="StartTime">2017-03-03T08:56:41.573-08:00</Column>
</Event>
<Event id="12" name="SQL:BatchCompleted">
<Column id="11" name="LoginName">SERVICEDESKSRV\servicedesk</Column>
<Column id="15" name="EndTime">2017-03-03T08:56:41.573-08:00</Column>
<Column id="6" name="NTUserName">servicedesk</Column>
<Column id="10" name="ApplicationName">Microsoft SQL Server Management Studio</Column>
<Column id="12" name="SPID">52</Column>
<Column id="14" name="StartTime">2017-03-03T08:56:41.573-08:00</Column>
<Column id="16" name="Reads">0</Column>
<Column id="18" name="CPU">0</Column>
<Column id="1" name="TextData">select SERVERPROPERTY(N'servername')</Column>
<Column id="9" name="ClientProcessID">3464</Column>
<Column id="13" name="Duration">259</Column>
<Column id="17" name="Writes">0</Column>
</Event>
<Event id="13" name="SQL:BatchStarting">
<Column id="1" name="TextData">SELECT
Thanks for your help.
Regards.