Access ошибка подключения к sql серверу

I use a Microsoft Access 2010 front end with linked tables on an SQL Server 2012 installation. Local network.
When Access Starts, a VBA script runs which connects to the SQL server and performs some checks.
I recently upgraded from SQL Server 2008 to 2012, that’s when the connection between client and Server started to fail intermittently.

When the connection between my client and the server fails, I see a generic message «SQL Server does not exist or access denied». This is covered in a Microsoft support article http://support.microsoft.com/kb/328306. The potential causes detailed in that article do not match the trouble I am encountering.

This connection issue is intermittent. The trouble arises about 3 times a week and lasts for about 30 minutes at a time. Between these 30 minute failures, the the system works perfectly.

My Current VBA Connection String: (have tried several, trouble persists with all of them):

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "Provider=SQLOLEDB;Server=Server3.companydomain.local;Database=My_Database;Trusted_Connection=Yes"

I hope that I can find something in the SQL Server Logs (which I do have access to) but I do not know which Log file to investigate.

Debugging

«SQL Server does not exist or access denied.» One error. Dozens of possible causes. Read on for a bunch of practical troubleshooting tips.

• 

Feb 28, 2022
• 6 min read

C:\> ping MyDbServer

C:\> ping 10.20.30.40

C:\> tracert 10.20.30.40

Test-NetConnection -ComputerName MyDbServer -Port 1433 

Have you ever encountered the issue of” SQL Server does not exist or access denied”? Then what’s your initiative to fix this out?  Do you know why you are getting these kinds of errors?

If no, then don’t worry as the following blog will help you in exploring more about this SQL Server does not exist or access denied error and ways to resolve it easily.

Practical Scenario:

Today about 1 pm some users started receiving the following error. We had not made any changes, so I am not sure what is going on. The weird part is that at this same time it started using the sql browser with a different port at this same time. Any ideas would be helpful. 

(FOC1400) SQLCODE IS 17 (HEX: 00000011) XOPEN: 08001 : Microsoft OLE DB Provider for SQL Server: [08001] [DBNETLIB][ConnectionO : pen (Connect()).]SQL Server does not exist or access denied. L (FOC1406) SQL OPEN CURSOR ERROR.

source: http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/1647017686

SQL Server Does Not Exist Or Access Denied is common Microsoft Data Access Components (MDAC) message that gives a clear indication that the computer is running

Microsoft SQL Server can’t be contacted. The following are the potential causes of this dbnetlib connectionopen (connect()). sql server does not exist or access denied error message.

Besides that error messages also have similar causes.

SQL Server is unavailable or does not exist.

The specified SQL Server is not found. 

Well, the above error doesn’t indicate the following:

• SQL Server Login process failure.
• To process the query, SQL Server doesn’t have that much permission. 
• You are unable to use SQL Server authentication because you have only got the permission of  Windows authentication.

Why You Are Getting “SQL Server Does Not Exist Or Access Denied” Error?

Well the following SQL Server does not exist or access denied errors occur when MDAC (Microsoft Data Access Components) message that shows the PC is running the SQL Server cannot be connected.

Here the possible causes for the occurrence of this error.

• Maybe the SQL Server is currently unavailable or doesn’t exist anymore.
• Remote connection is disabled for SQL instance.
• Port is not added to the firewall exception list.
• Maybe the IP address of the instance is blocked.

How To Fix The “SQL Server Does Not Exist Or Access Denied “Error?

Here are some best fixes to troubleshoot dbnetlib connectionopen (connect()). sql server does not exist or access denied error. Take a quick overview of it:

1: Check Whether the SQL Server is running or not

2: SQL Server instance is not available or does not exist

3: Remote connection is not enabled for a SQL Server instance

4: Port is not added in the firewall exception list

5: IP address of the SQL Server Instance has blocked the Firewall

6: connect remote SQL Server using Server Name

7: Allow the instance name to measure the connection information

Let’s discuss all these listed methods in brief….!

Method 1: SQL Server Is Running

The very first thing you need to do is to check that if the SQL server is running or not. to check this just type the “services.msc” command in the Run Window.

Method 2: SQL Server Instance Is Not Available Or Does Not Exist

Make a check for the connection strings and be sure that you are trying to connect the right server and it’s available on the network.

Method 3: Remote Connection Is Not Enabled For A SQL Server Instance

Check whether the remote connection is enabled or disabled. For this just open the SQL Server Management Studio -> Connect to SQL Server where it gets installed using SQL Server Management Studio ->Go to SQL Server instance property and make a check-in front of the Allow remote connection to this server option box.

Method 4: Port Is Not Added In The Firewall Exception List

It is also seen that SQL Server does not exist or access denied error message also encounters when SQL Server remote connection is enabled. But somehow the port is getting blocked by the administrator for security purposes.

By default SQL Server instance actually works on port no 1433, so you need to check that the port exception is get added to the firewall.

In order to check the port exception, just try the following steps:

1. • Start with the Control Panel -> and then open Administrative Tool -> after then select Windows Firewall with Advanced Security.
   • From the left panel, tap to the Inbound Rules, and from the right panel select New Rule…
   • In the New Inbound Rule Wizard window, make a selection for the Port and after then tap the Next button.
   • In the next tab, enter ‘1433’ in Specific local ports and tap the Next button.
   • Within the “what action should be taken when a connection matches the specified condition?” Section. You have to make a selection for the Allow the connection option and after then tap to the Next option.
   • Make a check for the Domain, Private, Public under which does this rule apply section? And tap to the Next option.
   • Assign a name for this and tap the finish button.

Method 5: IP Address Of The SQL Server Instance Has Blocked The Firewall

If you want to check whether the SQL Server does not exist or access denied 2016 error is occurring because of IP address, ping IP address on the command prompt like

Ping 100.10.10.10 –t

If you are getting a response from the server then it means there is no issue with IP Address blockage but if not, they need to add an exception.

Method 6: Connect Remote SQL Server Using Server Name

Step 1: First verify, the status of your protocol name.

After that, on the client-side, you have to make the alias.

Step 2: Now search for the cliconfig.exe present within System32 folder. After getting the cliconfig.exe  file, just tap on it.

Step 3: Now it’s time to make a TCP/IP alias. So, from the disabled protocols list choose the TCP/IP. After then hit on the Enable button.

Now you will see that your selected TCP/IP protocol will get added under the section of Enabled protocols by order.

Note: please ensure that the list doesn’t contain any Named Pipe. Suppose if in the list you get any Named Pipes then immediately disable it.

Step 4: Next move to the tab “Alias”. 

Step 5: After then hit the Add button.

Step 6 :  Now in the section of “Server alias” you require to enter the SQL Server database’s Server name.

Step 7: And choose the section TCP/IP present in the options box of Network libraries.

In the same add network library dialog box, you will see a connection parameter section. here in the server name you need to enter the IP Address of the SQL Server. After that hit the OK option.

Note: SQL Server’s default port number is 1433.

Step 8: Then tap to the Network Libraries for verifying the entries.

And you can get an easy connection with the remote SQL Server by making use of the Server Name.

Method 7: Allow The Instance Name To Measure The Connection Information

SQL Server 2005: In SQL Server 2005, for measuring the connection detail the server gives an instance name.

Solution: The user needs to remember one thing, that the instance name must be included in the assigned Server name.  As shown in the figure.

You can view the Server Name that is included along with the instance name i.e P6Instance.

Please Note: At the time you choose the by default instances that is already present on your PC. Then this will automatically take upgrade by the setup of SQL Server. Apart from this, a single system can only host just a default single instance.

Conclusion:

In the above blog, you must have got the possible reasons for SQL Server does not exist or access denied error and explained the solution to resolve the error.

Offtop  
До чего доводит лень…  

  Нужна была простая CRM система. Сделал в Excel — понял, что получилась фигня.  
Начал переписывать на Acess — понял, что долго и то же фигня.  
Сделал базу Acess и написал на VB .NET(первый раз столкнулся с VISUAL STUDIO И VB) собственную оболочку для юзеров.  

  Заработало, юзабилити отличная , но синхронизировал базы через FTP. Синхронизация шла через одно место.  

  Создал сайт на виндовс хостинге(ASP .NET тогда же впервые узнал, что такое бывает) с базой на MS SQL. Сделал WEB сервис, переписал базу (база одна, области «видимости» у всех разные), экспортировал данные из старой базы. Переписал клиентскую программу.  

  Сейчас 60+ пользователей работают в системе. В базе более 10 000 клиентов и более 150000 контактов. + появился сайт для пользователей системы с возможностью просматривать отчеты в онлайн и прочими плюшками. Начальство в восторге :-).  
А мог ведь просто сидеть и отчетики EXCEL от пользователей сводить, если бы лень не было.  

  P.s. понял, что VB фигня, читаю книжки по C#. Скоро начну все переделывать.  

  P.p.s. в прошлой компании бюджет на внедрение CRM был более 100 000 USD  

  Лень и только лень  ведут к прогрессу.  

  По существу вопроса. А почему вы на прямую к серверу не подключаетесь? Создайте в базе необходимые вьюшки или хранимые процедуры.