I am trying to setup an ODBC connection. The server is Windows Server 2012 running SQL 2014. The client machine is a Windows 8 64-bit PC.
On the client, I go into the SysWOW64 folder and run the odbcad32.exe as an administrator. When selecting the authentication method, if I choose WIndows NT authentication I have no option of typing a username or password. Therefore, I am under the impression that it is using the Administrator account that I used when I launched odbcad32.exe. Using the Windows NT authentication mode, I am able to connect to the database.
If I select, SQL Server authentication using the same Administrator credentials that I used to launch odbcad32.exe then the connection fails. I receive the following error:
SQL Server Error: 18456
I would like to use this method as I want to connect with a user that has limited rights to a database. I double checked my SQL server and it is configured as "SQL Server and Windows Authentication Mode."
Any help is appreciated!