December 1, 2009 at 5:45 pm
Hello, I have installed SQl server 2008 with Windows authentication method. I am having trouble getting an odbc connection so I would like to try connecting with SQL authentication. However, I cant find how to change to this method. Could any one help please?
December 1, 2009 at 7:13 pm
Right click on the SQL Server in SSMS and select properties.
In the properties dialog, you have multiple pages in the left page, select security page.
It will display the current authentication mode, select Mixed Mode and a series of OK buttons.
Restart the SQL Server (No need to reboot the machine) Just right click on the SQL Server and choose restart..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 1, 2009 at 8:42 pm
What error are you having with ODBC?
Bru's method will allow you to change the authentication method. Be sure that you set a strong SA password if you enable SQL authentication to prevent issues
December 1, 2009 at 8:47 pm
OK, so I have done that. My SQl Server Authentication is now set on "SQL Server and Windows Authentication mode". I dont see any where on the properties page where I enter the SQl username and password. Could you direct me again please.
BTW, my SQL server is on a Windows 2008 Small Business server which is not acting as a domain controller. So should I create a local user account to use for my SQL authentication, cheers
December 1, 2009 at 8:52 pm
With SQL Authentication, you now must add a "login" to the server and a "user" to the database you are accessing. go to the security folder under your server (databases, management, etc. One is security). Then you can right click and create a new "login", check SQL Auth, give it a password. You need to set a default database, and then map a user. The dialog will allow you to create a user in the database with the same name and map it.
December 1, 2009 at 9:20 pm
I have created the new login, given it a password and set the default database. I am confused about the next step of "map a user". Sorry
December 1, 2009 at 9:25 pm
In the screen where you are creating the Login, in the left hand pane you have multiple pages, in that choose User Mappings.
It has the databases in your SQL Instance, choose the database by selecting corresponding to your database and choose a suitable database role.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 1, 2009 at 9:27 pm
Edited : The reply was double posted.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 1, 2009 at 10:16 pm
I think I have done everything above, but my odbc is still not working. This is what I get
Connection failed:
SQl State 08001
SQL server error 1326
Microsoft [SQL Server Native Client 10.0]Named pipes provider Could not open a connection to sql server[1326]
Connection failed
SQl State HYT00
SQL Server Error 0
Login Time out expired
I am trying to create a FIle DSN using SQl Server native Client 10. Frustrated!!!!
December 2, 2009 at 5:19 am
Check in SQL Server Configuration Manager, under network settings. If Named Pipes are enabled.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 2, 2009 at 3:29 pm
I had previously enabled named pipes. Since I have changed the authentication method I can no longer connect using either method to SSMS. I noticed also that both SQL Server service and SQL Server agent service are no longer running and wont start when I try to start them manually. I seem to be locked out totally now....
December 2, 2009 at 5:32 pm
Look at the SQL Server error log and post the error message(s) here.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply