March 16, 2010 at 9:06 pm
Hi,
This seems so strange. I am using Windows 7(64 bit), and SQL Server 2008.
I have created a SQL Server Login named SampleSiteUser
I have assigned login's default database as SampleSite
I have given the login the db_datareader and db_datawriter roles
The default schema is dbo
When I attempt to login, I get the following error:
Cannot connect to SERVERNAME.
===================================
Login failed for user 'SampleSiteUser'. (.Net SqlClient Data Provider)
I have deleted and recreate the Login multiple times with the same result.
Could someone please tell me what I am missing.
Thanks,
Mark
March 17, 2010 at 9:27 am
It almost has to be the password. Are you sure your typing it correctlY?
March 17, 2010 at 9:34 am
One thing to make sure of is that the database principal has explicit Connect granted to it at the database level. It depends on how the principal was added to the database roles whether it implicitly granted Connect and I have seen instances where this was not granted even though the user was dded to roles within the DB.
Use SampleSite
Go
Grant Connect To SampleSiteUser
Go
March 17, 2010 at 9:36 am
Also make sure that SQL server is using Mixed mode server authentication..
March 17, 2010 at 10:08 am
Yes, I have recreated the user and password multiple times to make sure this was not the error
March 17, 2010 at 10:12 am
The user was created using SSMS, and I did not explicitly add Connect permissions. I had never need to do this in 2005 and didn't think I needed to in SQL Server 2008. I will check into this. Thanks for your feedback
March 17, 2010 at 10:40 am
Hey Mark,
If you created the user via SSMS then this probably isn't the issue, however still something to check. Since login failures are logged by default, are you seeing the login failures in the SQL log and if so, is there any more information provided there?
Thanks.
March 21, 2010 at 11:38 am
The problem was that Sql Server Authentication was not enabled. To fix I :
1) opened Sql Server Management Studio in Windows Authentication mode
2) right clicked the Server node in the Object Explorer and selected "Properties"
3) selected the "Security" page tab on the left
4) on the right under "Server authentication" I selected "Sql Server and Windows Authentication Mode" and clicked OK
5) right clicked the server node again and selected "Restart"
Thank you everyone for the time you took to respond. I normally install SQL Server in mixed mode. Apparently, I didn't when I installed 2008.
Mark
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply