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 windows update sql server connection error SQL Error SQL Server providers provider ole db provider odbc drivers error 80004005 microsoft jet database engine update windows sql server providers data name connection hosting 0x80004005 ms connect ole odbc name ms microsoft jet hosting error engine drivers db Database data connection connect asp error 80004005 0x80004005

Microsoft OLE DB Provider for SQL Server error ’80004005′
[DBNETLIB][ConnectionOpen (Connect()).]Specified SQL server not found.

CAUSE

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.

SOLUTION

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
  • 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

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.

User already exists in current database. SQL Server Error 15023 sql backup and restore database restore sql database from backup

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,

User already exists in current database. SQL Server Error 15023 sql backup and restore database restore sql database from backup

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

ALTER USER <Database_User_name> WITH LOGIN = <SQL_Server_LoginName>

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

EXEC sp_change_users_login 'Update_One', '<DB_USER_NAME>','<SQL_Server_Login_NAME>';

 

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

USE <YourDB_NAME>
GO
EXEC sp_change_users_login 'Report'
GO

 

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

DECLARE @username varchar(50)
DECLARE fixusers CURSOR FOR
SELECT UserName = name FROM master..sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name
OPEN fixusers
FETCH NEXT FROM fixusers
INTO @username WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT FROM fixusers
INTO @username
END
CLOSE fixusers
DEALLOCATE fixusers

 

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.

Solution 2 :

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.

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

SOLUTION :

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 Cannot open database requested by the login. The login failed. SQL Server Error 4060 SQL Error
    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. Cannot open database requested by the login. The login failed. SQL Server Error 4060 SQL Error

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.

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

The database CRM is not accessible. (ObjectExplorer)

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

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

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.

Cannot write property IsFileStream.This property is not available on SQL Server

While Creating a New Database on SQL Server 2012 using SQL Server Management Studio 2008 R2, you might get this error.

Cannot write property IsFileStream.This property is not available on SQL Server

Cannot write property IsFileStream.This property is not available on SQL Server 7.0. (Microsoft.SqlServer.Smo)

CAUSE

SQL Server Management is unable to set up the default property while creating a database using previous version of SQL Server Management Studio.

SOLUTION

To immediately fix this issue, we can create a database using a Create Database statement using Query Analyzer or we can upgrade a management studio client tool.

In my case, I create a Database using a TSQL statement, which worked perfectly fine.

Cannot write property IsFileStream.This property is not available on SQL Server

SSRS Cluster, How to Install and Configure SQL Server Reporting Service Cluster Configuration for High Availability VIDEO

How to Configure SQL Server Reporting Service on Cluster (SSRS Cluster) for High Availability ?

We can configure SQL Server Database Engine, Analysis Services and Integration Services  for High availability but how to Configure Reporting Services on Cluster (SSRS Cluster) for High Availability that too without using a any third party Load Balancing component.

The best way to configure SQL Services Reporting Services on cluster (SSRS Cluster) is to scale out Reporting services environment and have load balancer in place so that Load balancer can automatically forward the requests to multiple Reporting services, which are part of scale out deployment, using some algorithm like round robin etc.

I was being asked by one of my college, how can we configure SQL Server Reporting Services on a cluster (SSRS Cluster) so that an automatic failover can occur without using a Load balancer as the project was on low cost and a low number of active SSRS users.

Let’s take a quick example, where I going to go thru with a SQL Server Reporting Services Cluster Installation.

SSRS Cluster, How to Install and Configure SQL Server Reporting Service Cluster Configuration for High Availability VIDEO

In the above solution all Users will be accessing Reporting Services by a virtual name “REPORTServer” which has it’s own IP Address : 192.168.5.99

This is the similar configuration as we have for Clustered SQL Server Instance.

STEP BY STEP VIDEO Tutorial to Configure Reporting Services for High Availability in Clustered environment.

STEP 1 – Install Reporting Services on both Nodes and Scale OUT solution, Please check my previous article, ” Installing & Configuring SQL Server Reporting Services on Cluster Environment for High Availability“, here you will find that step by step instructions to configure Both Reporting Services (Nodes) pointing to common Report Database Server.

STEP 2 – Create a Virtual Server Name / IP Address (Client Access Point) in Clustered Services or Application group where you have SQL Services / resources

SSRS Cluster, How to Install and Configure SQL Server Reporting Service Cluster Configuration for High Availability VIDEO

SSRS Cluster, How to Install and Configure SQL Server Reporting Service Cluster Configuration for High Availability VIDEO

Enter the Virtual Name, which you want to use for your reporting Services and Enter virtual IP address which you want to configure for reporting Services. In Demo I used “REPORTSERVER” as name and 192.168.5.99 as Virtual IP address for cluster Services.

I have created this Virtual name / IP address for Reporting services in same Resource group as SQL Server as I wanted to go for automatic failover of this Virtual IP / NAME of reporting Services with SQL Server.

If you want reporting services Virtual name should go with SQL Server then add a dependency of SQL Services on Reporting Services IP address.

SSRS Cluster, How to Install and Configure SQL Server Reporting Service Cluster Configuration for High Availability VIDEO

NOTE - The limitation of this configuration is that SQL Services / Reporting services both will be used by users on Same NODE, which might result some resource contention.

To overcome this issue or in highly used reporting environment, create a dedicated resource group for Reporting services and add Virtual name / IP address in that group.

In this the limitation is you will not be able to create resource dependency and users will able to ping the name / address even if the SQL services are not running. But I believe that should be fine.

STEP 3 – Create a DNS Record and PTR record in DNS Server in my case this is “REPORTSERVER” and point that a Virtual Server Name / IP Address, which we created in previous step using (Client Access Point) in Clustered Services or Application group where you have SQL Services / resources.

SSRS Cluster, How to Install and Configure SQL Server Reporting Service Cluster Configuration for High Availability VIDEO

That’s it, you are done with SQL Server Reporting Services Cluster Configuration (SSRS Cluster) without load balancer. Now you can access reporting Services on any machine using virtual name and that will support the automatic failover too.

If you have question to any step, check the video, which is inbuilt in this post, that has Step by step instruction and work flow of configuring SSRS cluster without load balancer.

SQL AZURE pricing (PROS | CONS) – How costly is to have SQL Database on a Cloud?

SQL AZURE pricing (PROS | CONS)   How costly is to have SQL Database on a Cloud? SQL AZURE PROS and CONS sql azure pricing Compare SQL Server with SQL Azure  Hosting a Microsoft SQL Server Database on a cloud is very cheap, Anyone can afford to have a SQL Server database in Microsoft Cloud Environment. The cost starts from $5 per month, yes Microsoft has an 100 MB option, which costs only $5 a month.

Currently many customer / Business managers are unaware of this fact, how cost effective is to use Microsoft cloud / SQL Server Azure. specially for startups, where buying a software licensing is too costly. I prepared a small example to understand this fact. The very first question is

What are the Key factors for taking decision on hosting data on SQL Server Vs SQL Azure?

Compare SQL Server with Windows Azure SQL Database

Taking a decision between SQL Server (In house Hosting) vs hosting on Microsoft Cloud (SQL Azure) is not a big challenge.

As per my personal experience, decision of hosting Data on SQL Server vs SQL Azure is mainly derived by

  • COST  / Financial
  • Technical Limitations

Let’s understand these two in details by apple to apple comparison.

In this scenario, let’s take example for startup company and planned a database services solution 3-5 years time frame from cost prospective.

Assumptions

  • This is new company and gonna host a website for user interaction
  • The initial Database size would be 1 GB
  • First Year, marketing team is very active in advertisements etc, because product is still in development phase but application is available to users for first year. During this phase data growth is expected an avg of 1 GB per month. Thus by End of 1st year, our database size is expected to be 13 GB (1 Gb initial and 1 GB per month)
  • After a year company is expected to come up with big launch and will advertise in market and expected 5 time growth in second year i.e. 2 Gb Data would be added per month. Thus by End of 2nd  year, our database size is expected to be 13 GB (1 Gb initial and 1 GB per month)
  • Third and fourth year onwards company is expecting double his business and expected that 3 GB new Data will be added to a database.
  • As product is mature and reach it’s capacity, Year 5 company will have a bit slow growth but will look for more products to launch in market thus data growth would be 2.5 GB per month

So finally this is how the Database size growth chart looks like, based on above assumptions

SQL AZURE pricing (PROS | CONS)   How costly is to have SQL Database on a Cloud? SQL AZURE PROS and CONS sql azure pricing Compare SQL Server with SQL Azure

SQL AZURE pricing (PROS | CONS)   How costly is to have SQL Database on a Cloud? SQL AZURE PROS and CONS sql azure pricing Compare SQL Server with SQL Azure

Both two charts are based this Data for calculation purpose for calculating SQL AZURE Prices

Month Date Database Size Cost Per Month Cumulative Cost
1 1/1/2012 2 14 24
4 4/1/2012 5 26 90
7 7/1/2012 8 38 192
10 10/1/2012 11 48 328
13 1/1/2013 15 56 486
16 4/1/2013 21 68 678
19 7/1/2013 27 80 906
22 10/1/2013 33 92 1170
25 1/1/2014 40 106 1472
28 4/1/2014 49 124 1826
31 7/1/2014 58 134 2220
34 10/1/2014 67 143 2640
37 1/1/2015 76 152 3087
40 4/1/2015 85 161 3561
43 7/1/2015 94 170 4062
46 10/1/2015 103 179 4590
49 1/1/2016 111.5 188 5145
52 4/1/2016 119 195 5722
55 7/1/2016 126.5 203 6323
58 10/1/2016 134 210 6946
61 1/1/2017 141.5 218 7592

Hosting In house Database Server COST

If you plan to host a in house / in premises SQL Server Database server, then you are gonna pay approx $15,000 for this solution in 5 years time span.

Items Cost
Hardware Cost (Dell PowerEdge T410 Chassis)

Intel® Xeon® E5620 2.4Ghz, 8 GB RAM,  500GB with RAID controller Card

2,000
SQL Server License (4 Core) (1792 * 4) One Time 7,172
Date Center Hosting ($100 * 60) recurring 6,000
Total 15,172

I am assuming that you don’t want to take head ache for security / firewall / redundant Power,

Redundant Internet Backbones, DR etc and will prefer to host inside a vendor hosted datacenter and will pay a monthly rent.

So In our above example, a One time cost of $9,172 in buying software and hardware and will be paying $100 recurring cost for server hosting in Data center, which sill sum up to $15,172 and you need to manage your own server where you will be responsible for hiring a consultant, who will

  • Install and Configure SQL Server on a New Server
  • Will apply security patches, whenever there is a release
  • There is no DR covered in this, if Server fails, your business stops
  • Will buy some Antivirus to protect Server.
  • Need a DBA to for maintenance, who will be taking backup and other stuff.
  • There might be a possibility, Microsoft may come with New Version of SQL Server like SQL Server 2015 and might want to upgrade than , additional headache.
  • Need a Windows Consultant to manage windows Server / patches / maintenance same as SQL Server.

Conclusion on COST, the cloud hosting will be a preferred option, if you look at above example, where a SQL AZURE Database (Microsoft Cloud Cost for SQL Azure Database) is costing you $7,592 over a period of 5 years the same solution will cost you approx $15,172, if you decide to host your own SQL Server.

SQL AZURE Database is better Option than SQL Server database / hosting, considering Cost as primary factor and avoiding technical limitations.

But Costing if one part of solution, there are some technical limitations on SQL AZURE, which you should keep in mind, before taking a final decision.

Hosting Database SQL Server Database on SQL Azure / Microsoft Cloud or Building your own Database Server

Compare SQL Server with Windows Azure SQL Database

SQL AZURE PROS and CONS

Feature SQL Server (In house hosting) SQL Database on Microsoft Cloud (SQL Azure)
Number of Databases You can host multiple (32000+) Database on Instance as you don’t worry on number of databases, as you own the Server.

You can use “USE” <DatabaseName> to change current Database.

You bother to host number of databases on SQL Azure as you pay per instance of Database. Here you don’t own a Server, in fact you own a database.

The “USE” <DatabaseName> is not supported, as you own a only one database. you need to connect to Other Database by using alternative Connection String.

Database Size / Storage No size limits. DB can grow up to TB’s * The Web Edition Database can have maximum size of 5 GB.

* The Business Edition Database supports a maximum database size of 150 GB.

So any database beyond 150 GB is not supported by SQL Azure Database, you need to partition as work around with this limitation

Connectivity · SQL Server Management Studio

· SQLCMD

* The SQL Server Management Studio can be used to access, configure, manage and administer SQL Azure Database.

*The Management portal for Windows Azure SQL Database

* SQLCMD

Authentication * SQL Authentication

* Windows Authentication

SQL Server Authentication only
Schema / Structure No such limitation, you can design your own solution ALL tables must have a clustered index.
TSQL Supportability All TSQL Support is availbale There are some TSQL statements, which are not supported, here is a list of all such statements.

High Availability Features / Solutions High Availability Features / Solutions like

  • Replication
  • Log Shipping
  • Database Mirroring
Not supported as cloud services hosts inbuilt DR solutions
Scheduling Jobs / Taks We can use SQL Agent to schedule some tasks / activities SQL Server Agent is not available, thus Cannot run SQL agent or jobs on SQL Database
Server Level Customization you have full access to server, you can change server level properties, and see server level information. Some few system views are supported

 

Connection Limitations N/A Connecting to Windows Azure SQL Database by using OLE DB is not supported. Connections that have been idle for 30 minutes or longer are automatically killed.
SQL Server Integration Services (SSIS) Can run SSIS on-premise SSIS service not available on Azure platform

The Above facts are being taken from MSDN, “General Guidelines and Limitations, SQL AZURE

SQL Azure, is not only the Services which are being offered by Microsoft on cloud, As on date Microsoft offers following services as part of their Cloud Services Portfolio.

Standard Rates for various Cloud Services at glace:

Windows Azure

  • Compute
    • Small instance (default): $0.12 per hour
    • Medium instance: $0.24 per hour
    • Large instance: $0.48 per hour
    • Extra large instance: $0.96 per hour
  • Storage
    • $0.15 per GB stored per month
    • $0.01 per 10,000 storage transactions
  • Content Delivery Network (CDN)
    • $0.15 per GB for data transfers from European and North American locations*
    • $0.20 per GB for data transfers from other locations*
    • $0.01 per 10,000 transactions*

SQL Azure

  • Web Edition
    • $9.99 per database up to 1GB per month
    • $49.95 per database up to 5GB per month**
  • Business Edition
    • Maximum $225.78 per month a maximum database size, which is 150 GB, various prices can be check at Azure Calculator, SQL AZURE Pricing

    AppFabric

    • Access Control
      • $1.99 per 100,000 transactions
    • Service Bus
      • $3.99 per connection on a “pay-as-you-go” basis
      • Pack of 5 connections $9.95
      • Pack of 25 connections $49.75
      • Pack of 100 connections $199.00
      • Pack of 500 connections $995.00

    Data Transfers / Storage

    • North America and Europe regions
      • $0.10 per GB in
      • $0.15 per GB out
    • Asia Pacific Region
      • $0.30 per GB in
      • $0.45 per GB out

    Neudesic has built an Azure ROI Calculator, which provides an interactive tool to provide indicative pricing, which you check.

PowerShell script error – File not digitally signed : FIX

Today I was working on building PowerShell script to establish SQL Server Database Mirroring. To start with PowerShell, I tried to execute my first built of PowerShell Script on SQLPS, I received the following error.PowerShell script error   File not digitally signed : FIX system signed set executionpolicy set scripts script save remote Powershell Security powershell policy NOT loaded file execution scripts disabled system powershell set policy set executionpolicy remote signed save script cannot loaded because not digitally file execution disabled digitally cannot because  File C:\Temp\Mirroring\Script\DBMirrroringSetup.ps1 cannot be loaded. The file C:\Temp\Mirroring\Script\DBMirrroringSetup.ps1 is not digitally signed. The script will not execute on the system. Please see “get-help about_signing” for more details..
At line:1 char:47+ C:\Temp\Mirroring\Script\DBMirrroringSetup.ps1 <<<<    + CategoryInfo          : NotSpecified: (:) [], PSSecurityException     + FullyQualifiedErrorId : RuntimeException

You may also get Error “The execution of scripts is disabled on this system

As per the error message, the script is not trusted to be run on your system. This is because, my current security policy doesn’t allow to run any PowerShell Script, which is not Digitally signed.

FIX / Resolution

I have seen this issue in past where I get error “execution of scripts is disabled on this system”, to fix this, I modified security settings and asked PowerShell environment to run any PowerShell Script by setting ““ExecutionPolicy” as  Unrestricted, using the following command in PowerShell window “Set-ExecutionPolicy Unrestricted”

PowerShell script error   File not digitally signed : FIX system signed set executionpolicy set scripts script save remote Powershell Security powershell policy NOT loaded file execution scripts disabled system powershell set policy set executionpolicy remote signed save script cannot loaded because not digitally file execution disabled digitally cannot because

Note : Make sure, the command prompt / PowerShell window is opened with elevated privileges, I mean to say, run as Administrator Mode.

After that, when I ran my PowerShell script, that worked without any issues.