SQLNCLI for linked server returned message "Invalid authorization specification"
Thank you for looking at my question,
Scenario: The organisation for which I work has recently split into two discrete companies (Company A and Company B). I built a number of VB applications that interrogate / manipulate a SQL Server (Enterprise Manager) database running on SBS 2003.
The two companies have some common functions so I copied the Company A database to Company B's new SQL Server 2005 instance which is running on a new SBS 2003 server.
Have set up the relevant Company B users and ensured that they can see the Company B database via ODBC connection.
I ran one of the vb applications (compiled on my pc in Company A) on a pc in Company B and at the first point it should communicate with a database the thing crashed with the following error
Run Time Error '-21472179100 (80040e14)':
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider "SQLNCLI" for linked server "COMPANY_A_SERVER" returned message "Invalid authorization specification"
My code doesn't make any reference to the Company A server, so where does the .exe file pick that reference up?
I have attached the connection string I use in the code.
How can I make the executable run on Company B system?
Thank you
Scenario: The organisation for which I work has recently split into two discrete companies (Company A and Company B). I built a number of VB applications that interrogate / manipulate a SQL Server (Enterprise Manager) database running on SBS 2003.
The two companies have some common functions so I copied the Company A database to Company B's new SQL Server 2005 instance which is running on a new SBS 2003 server.
Have set up the relevant Company B users and ensured that they can see the Company B database via ODBC connection.
I ran one of the vb applications (compiled on my pc in Company A) on a pc in Company B and at the first point it should communicate with a database the thing crashed with the following error
Run Time Error '-21472179100 (80040e14)':
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider "SQLNCLI" for linked server "COMPANY_A_SERVER" returned message "Invalid authorization specification"
My code doesn't make any reference to the Company A server, so where does the .exe file pick that reference up?
I have attached the connection string I use in the code.
How can I make the executable run on Company B system?
Thank you
Set connSupplier = New Connection
connSupplier.CursorLocation = adUseClient
connSupplier.Open "PROVIDER=MSDASQL;dsn=ODBCName;uid=UserName;pwd=password;database=databasename;"
Brook Braswell
Is the users ODBC set to CompanyB?
we have a similar situation for a customer that has two company databases. We set a parameter on the application to the specific company we want it to run so that your application would see the Command$ and connect to the appropriate company
ASKER
Brook1966
Thank you for your reply,
The user in Company B has an ODBC connection on his machine that points to the Company B sql server.
"We set a parameter on the application to the specific company we want it to run so that your application would see the Command$ and connect to the appropriate company" - I thought I'd got around this because the two sql servers are completely separate, no links whatsoever.
I thought that by replicating Company A's database on Company B's server and using the same names for user logins, ODBC connections and connection strings everything would run without a hitch.
Thank you for your reply,
The user in Company B has an ODBC connection on his machine that points to the Company B sql server.
"We set a parameter on the application to the specific company we want it to run so that your application would see the Command$ and connect to the appropriate company" - I thought I'd got around this because the two sql servers are completely separate, no links whatsoever.
I thought that by replicating Company A's database on Company B's server and using the same names for user logins, ODBC connections and connection strings everything would run without a hitch.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Brook1966
You ask "When you test the ODBC for user on company B - do you have a way to Verify that the ODBC connects to the correct server?
Have you tried turning off the database for company A to ensure that the connection is going to the right place."
The ODBC link for the company B user can't connect to the Company A SQL Server as there is no physical link between the two - they are in different buildings about 500m apart
You ask "When you test the ODBC for user on company B - do you have a way to Verify that the ODBC connects to the correct server?
Have you tried turning off the database for company A to ensure that the connection is going to the right place."
The ODBC link for the company B user can't connect to the Company A SQL Server as there is no physical link between the two - they are in different buildings about 500m apart
ASKER
mmcdermaid
The user is connecting through a user dsn.
The user is connecting through a user dsn.
ASKER
Sussed it!
The problem arises because of the references to linked tables in the SQL Queries in the restored database(s). The table names still refer to dbo.Company A.TableName and so the query is looking for a link that doesn't exist.
The problem arises because of the references to linked tables in the SQL Queries in the restored database(s). The table names still refer to dbo.Company A.TableName and so the query is looking for a link that doesn't exist.
ASKER
Brook1966, mmcdermaid,
I found the cause of the problem. Please share the points for taking the trouble to reply.
Thank you
I found the cause of the problem. Please share the points for taking the trouble to reply.
Thank you