UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

2 Pages V   1 2 >  
Reply to this topicStart new topic
> Sql Server Does Not Exist Or Access Denied    
 
   
red68
post Oct 19 2011, 02:01 PM
Post #1

UtterAccess Member
Posts: 49



I recently installed sql server on a windows server and am trying to link to one of the tables on the test database that resides on the sql server. While doing this from aPC on the network I receive the following error message:
" sql server does not exist or access denied." I know the PC has access to the windows server itself because the access back end database is located there.

Can anyone please tell me what to look for.

Thanks,
Dave
Go to the top of the page
 
+
Jeff B.
post Oct 19 2011, 02:13 PM
Post #2

UtterAccess VIP
Posts: 8,546
From: Pacific NorthWet



Dave

When you set up that instance of SQL-Server, did you give yourself permissions on the table?
Go to the top of the page
 
+
red68
post Oct 19 2011, 05:34 PM
Post #3

UtterAccess Member
Posts: 49



I should have been clearer. I cannot get to the database to select any tables.
I tried to create a dsn from a PC on the network and receive the error message during dsn creation. I tried using the dsn I created when I first upsized and get the same error. I cannot find or access the sql server from a networked PC.
I originally upsized from the windows server itself because that is where the MS access back end database is located.
Go to the top of the page
 
+
Jeff B.
post Oct 19 2011, 05:50 PM
Post #4

UtterAccess VIP
Posts: 8,546
From: Pacific NorthWet



I should have been clearer ...

I wasn't suggesting that you reach the instance of SQL-Server via Acccess. I was asking about whether the SQL-Server instance was created with the permissions, and/or whether permissions were added/updated afterwards. That isn't something you do from Access, as I recall...
Go to the top of the page
 
+
red68
post Oct 19 2011, 06:39 PM
Post #5

UtterAccess Member
Posts: 49



The actual message box I receive is titled Microsoft SQL Server Login
and contains the following:

connection failed:
sqlstate:'01000'
sql server error:53
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
connection failed:
sqlstate:'08001'
SQl Server:17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL server does not exist or access denied.




When I installed SQL server I accepted the defaults. nothing has been changed.
Go to the top of the page
 
+
vtd
post Oct 19 2011, 06:47 PM
Post #6

Retired Moderator
Posts: 19,667



1. Which SQL version did you installed (2000/2005/2008 and MSDE/Express/Standard/Enterprise)?

2. Which Network Protocols did you enable for the SQL Server instance?

3. Did you specify one of the enabled Network Protocols as the Client Protocol to use when you tried to set up the DSN on your PC (i.e., the Client)?

4. Which Authentication Modes did you select for the SQL Server instance? Windows Pass-Through Authentication? SQL Server Authentication? Both?

5. On the DSN set-up dialog, did you try to supply the correct credentials required for the Authentication Mode selected on the SQL Server instance?

Access to the Windows server that hosts the SQL Server instance does not guarantee access to the SQL Server instance or the databases on the SQL Server...

Go to the top of the page
 
+
vtd
post Oct 19 2011, 06:50 PM
Post #7

Retired Moderator
Posts: 19,667



One more:

0. Do you know whether you have the latest ODBC driver for SQL Server on your PC, especially if you use SQL Server 2005/2008?

Go to the top of the page
 
+
red68
post Oct 19 2011, 09:24 PM
Post #8

UtterAccess Member
Posts: 49



0. I am not sure about the ODBC drivers. On the windows server I have Microsoft SQL Server ODBC Driver Version 03.86.3959. I do not know how to check the client.
1. SQL2005 express.
2. I do not know which network protocol I used.
3. when I create the dsn I do not see a place to specify a protocol. I just type the name and click the new button
4 pass-thru authentication if I recall correctly.


5. I do not see a place to do this.
t
Go to the top of the page
 
+
red68
post Oct 19 2011, 11:01 PM
Post #9

UtterAccess Member
Posts: 49



I tried creating a dsn using the control panel on the client and this is the result of the test data source button:
Microsoft SQL Server ODBC Driver Version 03.85.1132

Running connectivity tests...

Attempting connection
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

TESTS FAILED!

Go to the top of the page
 
+
vtd
post Oct 20 2011, 07:40 AM
Post #10

Retired Moderator
Posts: 19,667



0. Check ODBC Data Source Administrator to see if you have a driver called "(Microsoft) SQL Native Client" and try the "(Microsoft) SQL Native Client" in lieu of "(Microsoft) SQL Server". The 2 names seem to mean different versions depending on OS versions but "SQL Native Client" is the later name.

If you don't have it, search Microsoft Downloads for it and install the "(Microsoft) SQL Native Client" on your PC. I think the latest version is "SQL Native Client 10.0" which also works with SQL Server 2008.

I don't know the "Microsoft SQL Server ODBC Driver Version 03.85.1132". On my current PC with Windows XP, the "SQL Server" Driver version is 2000.85.1132 (last updated 14/Apr/2008) and the "SQL Native Client" Driver version is 2005.090.5000.00 (last updated 10/Dec/2010). I use "SQL Server" for databases in SQL Server 2000 and "SQL Native Client" for databases in SQL Server 2005.


2. SQL Server Express 2005 may have TCP/IP Protocol disabled by default. You may want to use the "SQL Server Configuation Manager" (usually installed with SQL Server Express 2005) to check for the enabled Protocols. IIRC, I had to manually enable the TCP/IP Protocol (my preferred Protocol) when I first installed SQL Server 2005 Express.


4. The "Server Properties" of the SSMS shows which Authentication Mode(s) are enabled on your SQL Server.

Go to the top of the page
 
+
red68
post Oct 20 2011, 04:26 PM
Post #11

UtterAccess Member
Posts: 49



I cannot find SQL Native client in the download center (IMG:style_emoticons/default/iconfused.gif)
Go to the top of the page
 
+
vtd
post Oct 20 2011, 04:33 PM
Post #12

Retired Moderator
Posts: 19,667



Try Feature Pack for Microsoft SQL Server 2005 - February 2007.

The fifth file on the list is sqlncli.msi which is an abbreviation for SQLNativeCLIent.

Go to the top of the page
 
+
red68
post Oct 22 2011, 12:29 PM
Post #13

UtterAccess Member
Posts: 49



I have installed SQL native client on another machine as well as another except on this machine I receive:

connection failed:
sqlstate:'01000'
sql server error:67
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
connection failed:
sqlstate:'08001'
SQl Server:17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL server does not exist or access denied.



TCP/IP is enabled on the server.

using SMSE I checked the properties and the authentication mode is windows authentication mode.
Go to the top of the page
 
+
red68
post Oct 22 2011, 05:10 PM
Post #14

UtterAccess Member
Posts: 49



I should have mentioned the second machine is windows 7. the first machine is XP.
Go to the top of the page
 
+
vtd
post Oct 22 2011, 05:25 PM
Post #15

Retired Moderator
Posts: 19,667



So the XP machine works fine but not the Win7 machine?

Go to the top of the page
 
+
red68
post Oct 22 2011, 09:01 PM
Post #16

UtterAccess Member
Posts: 49



Neither machine works, just a different error code 53(xp) vs 67(win7)
Go to the top of the page
 
+
vtd
post Oct 23 2011, 06:32 AM
Post #17

Retired Moderator
Posts: 19,667



1. Can you see the server that host the SQL Server process from your PC in the Network Neighbourhood?

2. Have you tried pinging the the server by server name or IP address?

3. When you installed the SQL Server Express, did you accept the default instance name or use another instance name?
Do you know how to check the instance name of the SQL Server, just in case it is not the default name?

4. I tested on my SQL Native Client with a deliberate incorrect SQL Server name and I got the 2 states '01000' and '08001' as per your posted so I suspect that you had the wrong name.

In the DSN dialog, did you select a server name from the drop-down list for the question "Which SQL Server do you want to connect to?" or did you type in the server name?

Go to the top of the page
 
+
red68
post Oct 23 2011, 03:18 PM
Post #18

UtterAccess Member
Posts: 49



I enabled the browser service on the server and was able to access the server with mixed results


On the Win7 machine I was able to link to the tables I upsized. On the XP machine I only see the tables that were created by the server itself such as :

dbo.spt_fallback_db
dbo.spt_monitor
INFORMATION_SCHEMA....

I need some more assistance in getting the XP machines running please.
Go to the top of the page
 
+
vtd
post Oct 23 2011, 05:53 PM
Post #19

Retired Moderator
Posts: 19,667



It looks to me that on the WinXP, your DSN points to the database "master" and not your required database...

Go to the top of the page
 
+
red68
post Oct 24 2011, 10:01 AM
Post #20

UtterAccess Member
Posts: 49



I did not specify a database when creating the data source. There is only one database "test" on there as of now.

I tried creating another dsn putting test in the attach database file name box when prompted but receive an error message "the database entered is not valid".

either way win7 pulled up the upsized tables, not XP. I know I will have to specify the database at some point because Production will be on there as well.

Thanks for your help.
Go to the top of the page
 
+

2 Pages V   1 2 >
Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 18th April 2014 - 04:06 PM