Is the authentication mode on the server set as mixed allowing both SQL Server and Windows authentication?
Where can the authentication mode be set to mixed? On the setup I see either the connection tab or the properties tab. On the connection tab I see name, notes, db type, db driver, server, port, db name, instance, userId, pwd, auto commit, permission mode, and authentication domain. On the properties tab I see db profile, driver properties, server authentication (which doesn't include a mode mixed or otherwise), qualifiers, physical connection, transaction, encoding, sql statements, connection hooks, color and border, sql commander, query builder, explain plan. I have a setup that I have tried SQL Server and auto detect SQL Server, server name fully qualified and not, port, db, with an instance and not. If I login it works because I have a domain account and windows authentication works. If system account logs in it doesn't work because the system account doesn't have a domain account and I don't see a way to specify mixed mode. I have googled dbvis and mixed mode and dbvis and authentication mode, etc. etc. etc. You know what? I downloaded MS SQL Server DB MGT Studio, logged in, connected the first time everything worked perfectly. I would like to keep using dbVis because that is where I have everything else setup, but come on man! SQL Server Authentication, where is it?
as Roger pointed out, it needs to be set on the SQL Server not in DBVis. Setting can be found in Server properties > Security > Server authentication. You can choose Windows Authentication mode or SQL Server and Windows Authentication mode (which is mixed mode).
See here for screenshot (first image in answer): https://stackoverflow.com/questions/4945815/sql-server-2005-mixed-mode-authentication
I am trying to understand how this works.
I have access to a SQL server DB. When I set up the connection via DBVisualizer (driver SQL Server (jTDS) and leave userid & password blank I connect as expected. I understand this to mean I am using Windows Authentication. This makes sense and is working as expected.
However, when I enter my MS id and password my connection fails. How come I can't connect by passing my username and password?
Login failed for user 'xxxxxx'.
Error Code: 18456
SQL State: 28000
My device is windows 10 and I am using DBVisualizer 10.0.15