Hi, I am presuming that a connection string and select SQL would be the same for a normal table as it would be a view table in SQL Server 2008. Is that right?

It's just that I have two databases on the same server and I can read from the normal one fine but the view tables one I am getting this error:

Microsoft OLE DB Provider for ODBC Drivers __ _ error '80040e14'_ __ [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider "SQLABCD10" for linked server "ABCDE01" returned message "Invalid authorization specification".

Cheers, Suzy

Yes querying views is the same as querying Tables.

Looks like your view is trying to reference a table in a linked server, one where the security is not set up correctly.

Look for tables in the View definition with the server specified ABCDE01.schemeName.tableName would indicate trying to read from table schemeName.tableName on server ABCDE01

Cheers Jamie This stupid IT guy is trying to blame my code for everything not working even though it works fine on my test server so I just wanted to check before my conference call that I'm not in the wrong. He did the same with the email set up too - amazing how after he gave me a different setting to try it all worked after on an email to the boss he said it was my code that was the problem!

Thanks again!

So what was the verdict Suzy? Was it stupid IT Guy's fault or what?

Oh without a doubt.

Ask him to script out the view and email you the result (a piece of SQL containingCREATE VIEW) so you can "install it on your local server".

Is that error not a permissions error?

He just has to give read permission on the View - which cant be so hard.

The ASPfaq site suggests that your connection string may be to blame:

Microsoft OLE DB Provider for SQL Server error '80004005' 
Invalid authorization specification

This usually means you left out some of the properties required to establish a connection. For example, you can reproduce with the following code: 

<% 
    set conn = CreateObject("ADODB.Connection") 
    conn.open "Provider = SQLOLEDB.1; Data Source = <ip address>;" 
%>

To correct, make sure you've included all necessary parameters (see Article #2126 for valid connection string attributes). 

And so does this StackOverflow question

Can you connect to the View DB yourself using Sql Server Management Studio (or similar management tool?)

Are you using the right credentials for that DB?

This is the connection string I am using:

Driver={SQL Server};Server=servername\SQLEXPRESS;Database=viewsdb;UID=sa;PWD=Password;

The IT person says he has tested the Connection with Excel Get data and also MS SQL Management studio from the CRM webserver to the CRMSQL and also to the Local SQLEXPRESS It works fine.

This is a slightly odd question.. but why do you need a DB specific to Views? Views are just representations of Table data.

Also - SQL Express is a bit of a pain to set up to allow remote access (even across a network) as it is intended not to be a production server. It is intended to be a dev/hobby environment.

I recall something about allowing "named pipes" being the issue the last time I tried to do this - granted it was several years ago with SQLExpress 2005.

It is very very easy to miss a couple of steps in the process of allowing access, so I think we are back to blaming the IT Person.

• also - did you set the user/password, or is it just for the purposes of this question? I ask because a website (or any app) should NEVER have "sa" user permissions; and "Password" is absurdly easy to guess.

The views db was created by someone else as it is pulling data from a database located elsewhere so that it can only be viewed for security reasons. All my testing is actually being done on the same server at the moment so I am only using localhost but still not getting anywhere.

Is it worth me trying different connection strings do you reckon?

It's just weird that I can read from another database fine located in the exact same place but not the view tables.

What DB type is the other database? Is it a full version of SQL Server? Or is it also on "WIN-V274SFZIPYP\SQLEXPRESS"

In SQL Express, you must enable TCP/IP or Named pipes for named connections. This step is easy to miss. As I recall it must be done in two places (in the 2005 version)

Do you have any control over the DB? Or are you working "blind"? If you cannot access the machine directly, I'd ask the IT person to do this:

What I would do is go to the actual database server,

• make sure "Named Pipes" is enabled,
• test on that machine,
• make sure SQL Server Managment Studio can connect with the connection details you used.
• Make sure a very simple ASP page can connect from that server
• Make sure a very simple ASP page can connect from another network server.

I am working completely blind and I have no access at all to the other db.

I was just told that I would be provided with the data I needed via a series of view tables and to read the data from them as I am not allowed to access the data directly.

If I go to the view tables in SQL Server Management Studio I can connect fine there but the simple test ASP page I set up on localhost that is only trying to show the data from one table just keeps coming back with:

Microsoft OLE DB Provider for ODBC Drivers __ _ error '80040e14'_ __ [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider "SQLABCD10" for linked server "ABCDE01" returned message "Invalid authorization specification".

but the connection to the other db on that server works fine. I can't figure who needs to be changing stuff to rectify this?

Cheers for keeping getting back to me on this scudsucker I really appreciate it.

OK - some stupid questions I must ask:

• Can you set permissions in SQL Server Management Studio for the Views?

• Is the SSMS running on your personal machine or on the server (ie, you are remoting into the box)

• Are you trying to set up the ASP page on your personal machine's localhost? Or on a server controller by IT Person?

• Is the "other db" that you can connect on the same "WIN-V274SFZIPYP\SQLEXPRESS" server

I'm beginning to think the problem is that the user you have in teh connection string does not have permissions to read the data needed to create the Views in the linked server (the one "pulling data from a database located elsewhere so that it can only be viewed for security reasons")

Hi scudsucker,

Thanks again for this

I'm remoting onto the box that is used as the web server and just using localhost on that machine.

Not sure about permissions, will ask as I haven't been given remote access yet for today.

The other db is a SalesLogix database on another box.

Does that make sense?

I've got access remotely to the server and asked the IT guy to show me how he was connecting so he showed me via Excel and said he had done it but when I asked him to actually show the data it came up with the same error as mine so it looks like my connection string isn't the problem.

Error:

Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for linked server "ServerName"

Originally posted by Suzy

when I asked him to actually show the data it came up with the same error as mine

Oh, LOL.

Sometimes I feel a job in McDonalds would be preferable to this

Haha!

I am willing to bet that he never actually tried to get the data.

For what it is worth I am pretty sure that the connection between your View DB and the original data tables needs permissions to read the original tables, and it does not.

In fact, the problem is HIS connection and not yours at all.

Thanks for all your help with this anyway. I'll keep you updated with what happens

yes - this a permissions issue between the linked servers, this is from StackOverflow:

In SSMS, Server Objects -> Linked Servers, Right-click on SERVER2 and choose Properties.

In the properties window, click on "Security" in the left hand panel.

In the section labeled, "For a login not defined in the list above, connections will:", choose the last option "Be made using this >security context:". Then specify a login and password for an account on Server2 with appropriate permissions for the task you're >trying to accomplish.

Cheers Techno

As you have probably guessed this has to do with the tables you are waiting for too!

It's all working now without me changing any code