SQL Server does not exist or access denied

Question

Hi All,

I am getting an error/warning message like below.Iam running the package by using SQL Agent.And the package is running successfully.Instead of success message iam getting below message.

SQL server ver:SQL server 2008 R2

OS:Windows server 2008 R2

Authentication: windows

Could you please help me how can I resolve this issue.

connection string value in ssis package:'Data Source=xyz;Initial Catalog=CALC;Provider=SQLOLEDB.1;Integrated Security=SSPI;Application Name=SSIS-SSIS_xyz-{3C728188-06E2-4643-8A81-D77775E92A6E}Conn_xyz;'

Executed as user: domain\xyz. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.4000.0 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  4:46:22 PM  Error: 2012-11-28 16:46:38.89     Code: 0xC0202009     Source: SSIS_XYZ Connection manager "Conn_XYZ"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL Server"  Hresult: 0x80004005  Description: "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.".  End Error  DTExec: The package execution returned DTSER_SUCCESS (0).  Started:  4:46:22 PM  Finished: 4:46:40 PM  Elapsed:  18.297 seconds.  The package executed successfully.  The step succeeded.

Answer 1

You're running the package via a Job. This means you're not running the package with your own account, but with a proxy or service account. That account doesn't have sufficient rights on DBNETLIB

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 2

Thanks a lot for your Reply..

How can I give Right permisions to  DBNETLIB .

Thanks,

Sekhar G

---

Answer 3

Thanks a lot for your Reply..

How can I give Right permisions to  DBNETLIB .

Thanks,

Sekhar G

---

The connection manager Conn_XYZ is using integrated security, so the user that runs the job step in SQL Server Agent should be given the right permissions.

Are you running the jobstep with the services account or a proxy?

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 4

From Proxy Account.

One more doubt but how the package is running success fully and retunning atatus as success.

Answer 5

From Proxy Account.

Check which credential is used in the proxy and then check which user is used in the credentials (see second screenshot). That user should have access to the server mentioned in the connectionstring. 

One more doubt but how the package is running success fully and retunning atatus as success.

Indeed sounds strange. Are you using the connection manager? Have you added package logging? Any error handling in your package?

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 6

Hi ,

Where can I see (see second screenshot) and the user id having admin rights.

Yes , I am using connection manager and Error handling in the Event handler tab.

Answer 7

Second screenshot in one of the earlier replies:

Also see 3) Running the package as a job under a proxy @
http://microsoft-ssis.blogspot.com/2012/09/who-is-running-package.html

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

Answer 8

I have given full control permissions to my id(which is using to run sql agnt job by Proxy a/c)  for 'DBNETLIB' dll.Still iam getting same error as I specified earlier.

Iam not able to find out this screen shot.Could you please upload it once again/provide the link to me.

Second screenshot in one of the earlier replies:

Answer 9

Hi Sekhar Reddy G,

You need to create a  proxy account firstly, please refer to the following link:
Running a SSIS Package from SQL Server Agent Using a Proxy Account: http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/ 

If you are on 64-bit computer, please heck the Use 32 bit runtime checkbox in SQL Agent, please see: http://sqlblog.com/blogs/john_paul_cook/archive/2010/03/24/running-32-bit-ssis-in-a-64-bit-environment.aspx

Thanks,
Eileen

If you have any feedback on our support, please click here

---

Eileen Zhao
TechNet Community Support

Answer 10

Hi Elieen,

Thanks for your reply..

I have created proxy account for running sql agent job.That user id having all permissions.

i have changed to 32 bit.but still iam getting below error..

 

Executed as user: INDIA\xyz. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.1600.1 for 32-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  12:21:22 PM  Error: 2012-12-12 12:21:54.96     Code: 0xC0202009     Source: SSIS_xyz Connection manager "Conn_xyz"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL Server"  Hresult: 0x80004005  Description: "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.".  End Error  DTExec: The package execution returned DTSER_SUCCESS (0).  Started:  12:21:22 PM  Finished: 12:21:56 PM  Elapsed:  33.399 seconds.  The package executed successfully.  The step succeeded.

Thanks,

Sekhar G

Answer 11

Hi All,

This is my config file.And my connection string is uploaded in next screen

Answer 12

When iam using these two images settings i am getting below message from sysjobhistory table

Executed as user: INDIA\xyz. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.1600.1 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  12:50:21 PM  DTExec: The package execution returned DTSER_SUCCESS (0).  Started:  12:50:21 PM  Finished: 12:50:22 PM  Elapsed:  0.624 seconds.  The package executed successfully.  The step succeeded.

Answer 13

Answer 14

Answer 15

If iam using second set of image settings I am getting below error message.

Executed as user: INDIA\xyz. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.1600.1 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  12:50:22 PM  Error: 2012-12-12 12:50:40.35     Code: 0xC0202009     Source: SSIS_REF_xyz Connection manager "Conn_xyz"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL Server"  Hresult: 0x80004005  Description: "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.".  End Error  DTExec: The package execution returned DTSER_SUCCESS (0).  Started:  12:50:22 PM  Finished: 12:50:40 PM  Elapsed:  18.33 seconds.  The package executed successfully.  The step succeeded.

Please help me what mistake I did in second set of images and how to resolve this issue.

Thanks,

Sekhar G