SQL Server authentication not working

[grunners22]

Hi

I'm running SQL server 2008 and am trying to use ODBC connections with SQL Server authentication. I can only get Windows authentication to work however, which is unsuitable for my application.

I've used the 'This account' section in the Log On tab of SQL Server Browser and the SQL Server in config manager, but these settings seem to be ignored. Only my windows administrator login is accepted.

Any help appreciated

Thanks

 

[grunners22]

In event viewer I'm getting the following error:

Type: Failure Audit, Login failed for user xxx Reason: Attempting to use an NT account name with SQL Server Authentication (CLIENT: <named pipe>).

Sounds obvious, but I've tried sa as a username and that doesn't work either.

 

[grunners22]

I've done a fresh install and am still having the same issue.

Can anyone tell me how I can specify SQL Server administrators (in the same way as you do when you are installing, in the database engine configuration screen) - I can't find the option and don't really want to reinstall again in order to get to it.

Many thanks

 

[SQL_Hell]

You want to add someone to the fixed server role sysadmin?

 

[grunners22]

I'm unsure as to whether this would solve my problem.

I just cannot get sql server authentication to work. Creating an ODBC connection only allows windows authentication, but as my users who will use this connection are not administrators, they cannot connect this way.

I need to get sql browser to accept connections using sql authentication.

Thanks

 

[SQL_Hell]

Ok, can you stop using ODBC connections for the moment and create a sql server login which is a member of the sysadmin role and try and connect to the sql server in management studio? using the login you just created

 

[grunners22]

I added the users to the sysadmin role and have used windows authentication in the odbc connections and it's working.

Thanks for the help

 

[SQL_Hell]

Oh my god, don't do that!!

You do not want your users all the be syadmins, they will have the power to delete / purge your server. what you have done is very bad

I only meant add a user to the sysadmin role for testing

 

[grunners22]

Sorry yes it's only still in the testing phase.

 

[SQL_Hell]

OK, for production you could put your users in a active directory group, and add the active directory group to the fixed database roles:

datareader
datawriter

Then add execute permissions to any stored procedures