March 31, 2009 at 6:23 am
Hi,
Just installed MSSQL 2008. I initially selected windows authentication for the authentication. I now need to set it to mixed mode. Right clicking on the database name and selecting properties, and then clicking on the "view connection properties" link takes me to where I want to make the changes, and it shows the current info, but none of the information is editable. I am logged in as the administrator. Another option was to right click on the sql server, select properties, then security and change the radio button to "sql server and windows authentication" but that doesn't reflect in the properties on the existing databases. Do I need to reattach them? Oh. I did see an option to fix this by editing the registry, but I do not see why I need to do it that way??
March 31, 2009 at 6:33 am
The properties view that you are referring to only shows how you are connected to that server so, that is not changeable. If you set the server to mixed mode and connect via SQL authentication you will see the connection properties reflect that as well.
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 31, 2009 at 6:35 am
The "authentication mode" setting is for the server and not for the database.
The authentication information that you see in the database connection properties is about your current login and current user viewing the properties.
--Ramesh
March 31, 2009 at 6:35 am
thanks, but how do I change each database to connect differently. currently all my recently attached DB's are set to use windows authentication. I would like for them to use the mixed mode.
March 31, 2009 at 6:41 am
Again, what you are viewing is not what options are available for connecting to a database. That is controlled at the server / instance level only. What you are viewing is the way that you are currently connected to that database. Connect as a SQL Authenticated account and this will show differently.
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 31, 2009 at 7:35 am
I'm trying to connect to a database using a website. defined username and pass in website. I created the same account defined in the website in the MS sql server management studio, under security->logins and every time I try to grant the the user the rights to a DB; It complains that the user, group or role already exist. But it doesn't seem to note that, and I still can't connect to the DB from the wesite. Any thoughts?
March 31, 2009 at 7:43 am
I would drop the user from the database then re-add that user based on the login you just created. My guess is that the database you are working with was attached from somewhere else and you have an out of sync login condition. There are scripts to alleviate that but to get past this one issue do what I stated first and you should be ok.
I would then validate that you can connect to the database server and the database using that login from SSMS just to make sure things are working correctly. Then go to the website and work on the connection properties there.
Another great tool that either Andy Warren or Brian Knight recommended a long time ago on this site is as follows;
On your desktop create a new text file. Rename it to something like Connect.udl (the .udl is critical). Double-click on that file and it will walk you through connecting to a data source. Follow the tabs at the top and when you are completed and have tested your connection you can close it. Now right-click on that file and open with notepad. What is displayed is a perfect connection string that you just created and should work great from your web page, etc.
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 31, 2009 at 7:44 am
It means that the user already exists in the database to which you are trying to grant access. This happens when you restore a database with existing users, the users in the new databases get orphanedm i.e. the SID of the database user does not match with SID of the server login.
To fix the issue, you either have to delete and re-create the database user or fix the database user by executing the procedure sp_change_users_login.
EXECUTE sp_change_users_login 'AUTO_FIX', 'myuser' --replace myuser with the your application user
--Ramesh
May 21, 2009 at 2:23 pm
while installing if you installed only windows auth mode then you need to go for registry of sql server find auth mode change the bit to '2' then restart services.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply