Unanswered: OLEDB Provider, Access Denied when using Named Instance
I have this strange problem while connecting to a SQL Server 2008 Enterprise Edition from a Application Server (Windows Server 2003 Enterprise Edition).
I have a web.config file specifying the connection strings to connect to various databases.
The following is a sample connection string which works :
<add key="ConnectionString_dbfABS" value="PROVIDER=SQLOLEDB;server=SERVER IP ADDRESS\SQLInstanceName,PortNo;user id = dbtest;password=dbtest;database=dbfABS" />
But we have a restriction that we should not use IP Addresses while connecting to a database server and have to use the servername\Instance name instead.
So i tried changing the above connection string with server = SQLSERVERNAME\SQLINSTANCENAME.
When i do that,i get a connection to sql server cannot be made <or> Access denied error.
Note : 1.There is no firewall set on the server running SQL Server.
2.The port has been configured/opened on the DB Server using the SQL Configuration Manager
3.The TCP/IP Port has been enabled.
I get this error only while using OLEDB Provider.Otherwise for connection strings as below :
<add key="ConnectionString_dbfExample" value="server=SERVERNAME\INSTANCENAME,PORTNO;user id = Dbtest;password=DbTest;database=dbfExample" />