I've recently inherited a SQL server (2008, not R2) where some users connect to the server using the computer name and some users connect using a DNS alias. There is only the default instance on this server. Is there any way I can log or audit the connection string or target of the connection to see whether a user is connecting using the DNS alias or directly to the server?
I didn't see this info when I tried to run a login trace, or when I looked in the sysprocesses view and DMVs.
You have a couple of options here. First, and maybe best, you can use Logon Triggers. Logon triggers fire when users logon (sounds like the perfect name :) ). You can handle the logon tracking through this. Look here for more information about what you can do and what you can see when using logon triggers.
If that's not the way you want to go, you can look at server side traces. You can configure a trace to capture logon events. Look at this for information about server side traces.
The Logon Triggers will provide the least impact on your SQL Server. Server side traces can create performance issues if not appropriately configured. And, avoid using the client-side trace tool (SQL Profiler) in a production environment.