Score:0

How to test Windows Service Account can login to SQL Server database?

de flag

How to test that a Windows AD service account has been granted access to read from a SQL Server database server via Windows Authentication ?

This would need be an isolated test independent of any operations running within the Windows Service.

Doug avatar
in flag
I'm failing to understand the question... why not just login with the service account and test it? Add some detail here to describe what you are trying to do, and why it is special or unique that requires asking for help at all.
BaltoStar avatar
de flag
It's not always possible or practical to interactively login as service account. What if the service account password is not known or managed by the system ? What if it's not convenient to logout from working session and login as service account ?
Score:2
cn flag

From PowerShell:

Import-Module SqlServer
Invoke-Sqlcmd -ServerInstance "DBSERVER.COMPANY.COM" -Query "SELECT TOP 1 * FROM DBName.dbo.TableOrViewName;" 
BaltoStar avatar
de flag
Thanks Greg, but unless I'm misunderstanding something, your PS is not executing under the context of the service account. Are you implying that I can somehow login to PS as the service account ?
cn flag
@BaltoStar: *Windows Authentication* means it will authenticate with the credentials that the script is executed under. Specifying the credentials (username/password) for the service account is possible, but that isn't Windows Auth. "If Username and Password are not specified, this cmdlet attempts a Windows Authentication connection using the Windows account running the Windows PowerShell session. When possible, use Windows Authentication." https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps
Score:1
in flag

Shift-Right-Click on the icon for your favorite tool of choice (or command prompt, PowerShell, etc.) and select "Run as a different user". Enter the service account credentials, login as usual, execute a representative query.

The same thing can be done from command line using the runas command:

RUNAS /noprofile /netonly /user:<service account> <program>
Score:0
us flag

This is for example possible with SQL Server Profiler on the SQL server.

With this tool you'd be able to set filters so that only the desired transactions will be shown.

It will show among other things: Source Host, Source Program (your Service), Account, Destination DB of a login or transaction.

BaltoStar avatar
de flag
Thanks for the response, but I'm looking for a simple way to directly test authentication & authorization : login + select * from db1.dbo.table1
I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.