I am working on a c# application that was originally developed using the provider SQLNCLI11. Now the client is requesting to use the SQLOLEDB.1 provider. The development environment sql server is running locally and the sql server name is (LocalDb)\v11.0 using Integrated Security SSPI When the provider is SQLNCLI11, everything seems to work properly. When changed to SQLOLEDB, I receive the following error
[DBNETLIB] COnnectionOpen (Connect()).] SQL Server does not exist or access denied.
My connection string is:
Provider=SQLNCLI11;Data Source=(LocalDb)\v11.0;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=testdbname
Any idea why I am receiving this error when the provider is set to SQLOLEDB?
Go to a cmd window and type in hostname. Use that name instead of (localdb). So use this hostname\v11.0 but replace hostname with whatever value hostname returns from a command prompt. I am surprised the installation allowed you to create an instance name with a period in it.
If that doesn't work, use hostname,portnumber. You can lookup the port number in the SQL Server Error Log. It displays it at startup. If your instance is using a dynamic port, you'll want to change that to a static port so that you don't have to change the connection string. Alternatively you can create an alias so that you can just specify hostname.
That did not seem to work. I was not able to connect to the sql server using the hostname. I am also using SQL Membership Provider. If I use Provider=SQLOLEDB.1 or Provider=SQLOLEDB in the connection string then I get the following error “Keyword not supported: 'provider' “ when calling Roles.GetAllRoles(). If I remove the Provider from the connection string, the membership and role provider works but then I get the following error when trying to connect through the DAL: “An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'.”
This same code works on the development server (not my local) pointing to a Sql Server on a different box. So it seems like it has something to do with my instance of sql server.
I suspect the period in the instance name is causing the driver issues. Could be wrong, but I've never seen an instance have a period in it. And I've supported thousands of servers.