Login Problem when using Windows Authentication only

Question

Login Problem when using Windows Authentication only

Jessica-240533

SSC Eights!

Points: 879
More actions
June 20, 2005 at 4:32 pm

#167507

We are setting up a new database and trying to use Windows Only authentication, rather then mixed mode. We have created a domain user ID, domain\ID. The ID was set up as an SQL Login and permissions were granted to the particular database. We cannot login with this ID and the only error message we get is access is denied, SQL Server does not exist or login is invalid. When we change the authentication to mixed mode, create an SQL ID, and give that ID the same permissions on the database as the windows ID, we are able to login fine.
Am I missing something here? I have been reading something about trusted connections but we are just using ISQL on the same machine the SQL Instance is running on.
Any ideas you have would be appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

EdVassie SSC Guru Points: 60472 More actions · Answer 1

When you use Windows authentication, the accounts must be set up as domain\ID in SQL Server. This can easily be done in Enterprise Manager.

I strongly recommend that you assign your users to NT groups, and use Windows group level authentication in SQL Server. The account entries in this case are in the form domain\GROUP. We use Windows authentication with group authorisation for everything bar some 3-party applications that only support mixed mode SQL logins.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Ian Yates SSCoach Points: 19738 More actions · Answer 2

When you say you can't login with the ID, I assume that the ID you set up in SQL Server is the one that you are logged in to Windows when trying to access SQL.

Using Windows auth, you cannot pass SQL Server a username/password - that is only for SQL Auth. Windows auth will use the credentials of the currently logged on Windows user. Thus it is more secure as your app does not get involved with passwords, passwords are not transmitted, when a user's windows account is revoked, so is their SQL access, etc.

Try going to a command prompt, logged in as your windows account set up in SQL, and type

OSQL -E

that will use your windows credentials

Typing

OSQL -U [username] -P [password]

will use SQL Server credentials - I assume you are trying to do this using your windows user account's username & password.

Cheers

Jessica-240533 SSC Eights! Points: 879 More actions · Answer 3

Thank you for the information and we will try the OSQL you have provided.

Yesterday, using ISQL, we were trying the following ISQL -U domain\id -P password and it would not connect. Is that different then what you specified in the OSQL commands?

Thanks.

Ian Yates SSCoach Points: 19738 More actions · Answer 4

OSQL and ISQL are pretty much the same - one uses ODBC and the other uses DB-Library to connect to SQL... I've used osql out of habit

In any case, again you cannot ever specify a windows username & password to SQL server - it will, transparently if you like, use the credentials of your current windows login. Try logging on to the windows using your windows account and use either ISQL or OSQL with the -E option...

I'm not sure if you are writing an app or just looking to run some scripts, but if you are writing an app using ADO, ADO.NET, etc, make sure you specify that you are using integrated security (aka windows security)..

Hope all works out for you