We help IT Professionals succeed at work.

SQL Server 2005 - Permissions Issue. Error '80040e4d'

1,999 Views
Last Modified: 2011-10-19
Error Message:
   Microsoft OLE DB Provider for SQL Server error '80040e4d'
   Login failed for user 'SERVERNAME\USERNAME'.

Connection String:
  "Provider=SQLOLEDB;Server=SERVERNAME;Database=websitename.com;User ID=SERVERNAME\username;Password=password"

SQL Server Information:
   Microsoft SQL Server Management Studio      9.00.3042.00
   Microsoft Analysis Services Client Tools      2005.090.3042.00
   Microsoft Data Access Components (MDAC)      2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
   Microsoft MSXML                  2.6 3.0 4.0 5.0 6.0
   Microsoft Internet Explorer            7.0.5730.11
   Microsoft .NET Framework            2.0.50727.1433
   Operating System                  5.2.3790

Other Information: Single-machine setup. SQL Server is same as IIS box.

This is my first time in SQL Server 2005, and I have very little experience with prior versions. I think I have enabled TCP/IP / Named Pipes through Surface Area Configuration. I have reset the password to ensure that it is correct. In Management Studio, if I expand 'Security', then expand 'Logins' and double-click on my user, I see the following information:
 - General Tab: Default database: the database I want to connect to
 - Server Roles: I have all of them checked
 - User Mapping: databaae is checked, and 'default schema' is db_owner

What are my options to fix this? Please bear in mind that I am a complete novice in SQL Server, so you may have to talk to me as if I were 5 years old (not kidding).

Thanks,
Matt
Comment
Watch Question

CERTIFIED EXPERT
Awarded 2008
Awarded 2008
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Did you look at Server Authentication mode. It is possible that you are in Windows authentication mode only. If that is that case, you need change to Mixed mode (SQL Server and Window Authentication mode).

Check by going to Server Properties  and clicking on Security page. (I have attached image)

Change to "SQL Server and Window Authentication" mode and Restart the server.



SQL05-Mixed-Mode.jpg

Author

Commented:
sureshkgupta1, I verified that it is in mixed mode.

chapmandew, if I go with your route, my SQL query is now generating an error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e09'
[Microsoft][SQL Native Client][SQL Server]The SELECT permission was denied on the object 'tb_locations', database 'gogodfathers.com', schema 'dbo'.
/Default.asp, line 21
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
Well, at least you are getting somewhere....you've connected.  Now you just need to give that user the necessary permissions.

grant select on tb_locations to yourusername

Author

Commented:
chapmandew: What does that mean? You must've missed the part in my original post where I said that had absolutely zero SQL server experience. I'm suprised with myself for creating a SQL account and associating it with the database.
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
NO problem...that was just the statement to execute in a new query window in Management Studio to give your user name (that you connect with) the permission to read from the table that you're trying to read from.

Author

Commented:
I think I found it under the permissions tab for the database. If i select the new SQL-only user, then I have checkboxes down below, and they are marked appropriately (INSERT, SELECT, UPDATE).

Now I have to figure out how to query for dates in SQL. In Access I had to surround the date with '#", but SQL is generating an error.....grrr...I wish I didn't have to do this.
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
:)  You'll get through it.  Just post new questions when you run into problems...everyone on here is pretty good w/ SQL, so we'll help you out.

Out of curiosity, how come you're having to do this if you have no experience w/ it?

Author

Commented:
So back to the original question a bit:

You are having me use: Driver={SQL Native Client}; in the connection string, but everything I read prior to starting this told me to use Provider=SQLOLEDB; as it was the best option (of course they did not elaborate, other than saying it was faster). What is the difference?

Author

Commented:
I'm doing it because I generally use Access, but most of the sites I develop are intranet-only, or need to support only a small number of concurrent users. However, a current project is far more expansive, and I've noticed some longer page loading times (pages where there are multiple, complicated queries).
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
Different connection protocols...the first is ODBC (what I've always used)...the 2nd is OLEDB....either one is fine to use.

Author

Commented:
Well it is working (at least until I start screwing with the queries). I need to go answer some questions to get more points to ask questions though...

Thanks for the help,
Much appreciated.

Matt
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
Good deal....go get yourself a Teach Yourself SQL book....EASY language to learn, but difficult to get good at.  :)

Author

Commented:
Haha! I don't want to get good at it, then they'll expect me to do it all the time!
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
...then you'll have to ask for a lot more money.  :)

Author

Commented:
For anyone reading this thread for assistance, I had to change my connection string, as I was having an issue.

I had to change it to: "Provider=SQLNCLI;Server= rather than "Driver={SQL Native Client}, as Classic ASP does not handle the nVarchar(MAX) with the (SQL Native Client).
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.