How to write a training report

how to write a training report

Microsoft OLE DB provider for SQL Server error 80004005

While browsing a ASP site, you end up getting SQL Server error 80004005.

Microsoft OLE DB Provider for SQL Server error ’80004005′

[DBNETLIB][ConnectionOpen (Connect()).]Specified SQL server not found.

Microsoft OLE DB provider for SQL Server error 80004005, primary occurs because ASP page is unable to connect to SQL Server using connections settings, which you used in connection String.

There are multiple cause, which can result, microsoft ole db provider for odbc drivers error 80004005 or Microsoft OLE DB provider for SQL Server error 80004005.

I would suggest you to quickly go thru thru this checklist, to ensure everything is correct.

CHECKLIST to FIX Microsoft OLE DB provider for SQL Server error 80004005

  • Make Sure there is no syntax error in your connection String. you see multiple examples of Connections Sting here
    • SQL Server 2012
    • SQL Server 2008
    • SQL Server 2005
  • Ensure that you have used the Correct parameters in Connection String and there is no Typo Error in SERVERNAME, USER NAME, Datasource name
  • Try connect to SQL Server Directly from SQL Server Management Studio using the details, which you specified in the connection string.

There might be a possibility that you can connect to Server by IP address but not by names or you can connect to SQL Server just by typing FQDN which is SQL Server name.DomainNAME but not by just Server NAME. you have specified the instance name. if you are connecting to sql server instance.

  • There might be a possibility there is firewall between Application web server. your Machine and SQL Server, so it’s better to try the validate connection directly from Application Server / IIS Server. If SQL Server Management studio is not installed, you can use ODBC from Control Panel\All Control Panel Items\Administrative Tools\Data Sources (ODBC)

If that success, you should be good now but if you fail to connect to SQL Server from application Server using ODBC then validate

  • your SQL Server is running
  • you can connect to SQL Server using same credentials in connection string directly at SQL Server, if not
  • Check SQL Server Authentication setting and make sure it’s running in MIXED MODE
  • you might want to check Cannot connect to SQL Server, ERROR 2
  • Then try to FIX connectivity by verifying SQL Server is using TCP IP protocol
  • SQL Server is enable for remote connections
  • SQL Server is running port 1433 else specify port in connection string
  • Certificates are working and are not expired, incase Certificates are used.

Ideally, the above checklist should be able to fix Microsoft OLE DB provider for SQL Server error 80004005

Keywords. ole db provider odbc drivers error 80004005 microsoft jet database engine update windows sql server providers data name connection hosting 0×80004005 ms connect

User already exists in current database. SQL Server Error 15023

BY Mahesh Gupta on August 9, 2012 0 comments

This error usually happens after you have restored a database from a backup from one server to another. When you move a databases from one SQL Server to another, and your application might get errors saying “ Login failed for <user> ” and when you check Database Security and you find that login was already in the database with appropriate rights to allowed objects in the database but you still end up this Error.

Error 15023: User already exists in current database.

Cause : The problem occurs because database users which you see inside the database are “orphan “. Orphan users are those users for which there is no login id associated with this database user. This is true even if there is a login id that matches the user, since there is a GUID (security ID SID) that has to match as well.

This can be easily understood by following picture,

This picture has been taken from Jonathan

User – means a user which you find inside a database

LOGIN – A login credentials which you see in security section of Server and use to connect with SQL Server.

Solution / FIX :

The solution is pretty simple, we need to make sure that SID of Login (Server Principal) matches with SID of database user.

To do so we have multiple Options

SOLUTION 1. Maps an existing database user to a SQL Server login One by One

Execute the above code, to make a mapping between database user and server login. In case you are using SQL Server 2005 SP1 and older versions then use the following SP

IF you want to find all Orphan Users in current database, Database Users that are no link to SQL Server Login, use the following SQL statement.

SQL statement, which will list all Orphan Users

In case you have multiple Orphan users, then it’s pretty time consuming to map user to login one by one, to overcome this problem, we can do all at once using following TSQL statement

this TSQL statement, will check all Logins, weather they have a similar name Database User or not, if it found, this will map the login with user.

NOTE - this Script might give you some errors, if a login doesn’t have a corresponding user, which should be fine and you can ignore those errors, as it has already fixed, what was matched.

Drop the Database User and create again and give necessary permission.

Cannot open database requested by the login. The login failed. SQL Server Error 4060

CAUSE. This problem occurs because login that you used to access database does not have sufficient permissions.

You need to grant permission to the user to access database and/or to execute stored procedure.

How to grant reader/writer access to SQL database:

    1. Log on to SQL Server Management Studio by going Start >> All Programs >> Microsoft SQL Server >>> SQL Management Studio. and suppl user credentials & click OK .
    2. In Object Explorer, expand Secuirty. expand Logins, for which you are getting the Error and then expand Security .  Make sure the Login exists else add that user by right click and add and then
    3. Right-click Login. and then click Properties for the USER NAME / LOGIN NAME for which you are getting the Error “Cannot open database requested by the login. The login failed”, SQL Server Error 4060
    4. In newly opened screen of Login Properties, go to the “User Mapping” tab. Then, on the “User Mapping” tab, select the desired database – especially the database for which this error message is displayed. On the lower screen, check the role db_owner. Click OK.

This should fix this error but consider this security recommendation

Recommendation. – Assigning a a DBO privileges to NETWORK SERVICE is security violation as mostly all security standard. Change the application / Service startup account to to dedicated Service account and then give these privileges to dedicated service / application account.

The Similar error has different interfaces. for example, In SQL Server 2012, this get the following Error Message, when you try to access a database, where user don’t have permission for specified database.

The database CRM is not accessible. (ObjectExplorer)

OR this error, when you access the same from Query Window

Msg 916, Level 14, State 1, Line 1

The server principal “hyperion” is not able to access the database “CRM” under the current security context.

OR when you try to access from front End / Application

Cannot open database requested by the login. The login failed. Login failed for user ‘NT AUTHORITY\NETWORK SERVICE’

The same solution applies to all these errors.

Source: www.sqlserver-training.com

Category: Bank

Similar articles: