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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Connecting Access To Ms SQL Express 2008r On Another Computer, SQL Server 2008 R2    
 
   
firlandsfarm
post Jul 17 2017, 11:33 AM
Post#1



Posts: 246
Joined: 28-April 02
From: Heathfield, England


I have just introduced a new Windows10 laptop (BIGSAM) into my family. The SQL server runs on my old Windows10 laptop (LAPTAB) and with my very limited knowledge I thought ... SQL Server ... Network ... no problem! Silly me!! smile.gif Try as I have I cannot get Access 2016 on BIGSAM to connect to the SQL database on LAPTAB. frown.gif I might add I had no problem before when connecting within LAPTAB.

BIGSAM and LAPTAB are happy to chat to each other, exchange files etc., the only quirk I can see is that File Explorer on BIGSAM shows LAPTAB as being on the network (Workgroup: "FIRLANDSFARM") but LAPTAB can only see itself on it's File Explorer so I am having to use BIGSAM to initiate all file exchanges. However as it's the Access on BIGSAM that I want to use to connect to the SQL database on LAPTAB I don't think this is a problem.

One area I have tracked down that can be a source of problems is computer and SQL logins. I understand using PIN's to log in can cause problems so I have removed the PIN from my account on BIGSAM and given both computers my MSN Account as the logins as I have read that using MSN logins can help what many see as a problematic task.

I have also tried to make sure the Server is set for a network and think I have changed this correctly ... according to SQL Server Configuration Manager the database protocol "TCP/IP" is enabled and "All ports" is set to 1433 (I cannot see how to configure Access to communicate via Port 1433, does it use this as a default?) BIGSAM's IP address is shown on the Hub as 192.168.1.218 but this IP address does not show in the list of IP addresses for the Server TCP/IP properties. I have therefore set "IPALL" to TCP Port 1433 (after the found settings for TCP/IP were failing) and erased the TCP Dynamic Ports value (51636) . I restarted the service via the SQL Server Services tag after changing the settings.

The last piece of information I can offer that may be relevant is that the SQL Server is shown as Running with the "Log On As" showing "LocalSystem" ... I tried changing it to Network Service (it seemed a reasonable thing to do!) but then the app on LAPTAB that uses the SQL database couldn't connect! Could it be that the database is locked to LAPTAB?

Please ask if any additional info would help, I have no experience of SQL servers and network administration so fully accept what I have typed may give rise to more questions than what it has informed. smile.gif







--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
GroverParkGeorge
post Jul 17 2017, 12:35 PM
Post#2


UA Admin
Posts: 30,445
Joined: 20-June 02
From: Newcastle, WA


There are a number of things to consider. You've addressed some.

One additional thing you need to take care of is the Windows Firewalls on both computers. Make sure there is a rule on BOTH (and both incoming and outgoing rules) which allows them to communicate via the port you selected, ie the default 1433 you chose.

--------------------
Go to the top of the page
 
firlandsfarm
post Jul 17 2017, 03:28 PM
Post#3



Posts: 246
Joined: 28-April 02
From: Heathfield, England


Thanks for your input George ... I hadn't considered the Firewall on both, good point but both are 'open' for SQL Server. But I can't see how to clear the way through the ports, I'm using Windows Defender and it only seems to ask for permission per app, I didn't notice anything about a route.

In playing around with settings sometimes I get a "cannot connect" response, sometimes a "login timeout" and sometimes 'something else' (I can't remember the detail I have seen so many error panes!)

I must admit I have no idea what the Select Data Source dialogue box is referring to ... it may as well be written in Swahili and so claimed explanations on the Internet help very little. They never finish the help off and just leave you hanging. For example ..."Select the data source that describes the driver you wish to connect to." Select the data source! That's the SQL Database and when I go to the folder I think it's in it doesn't show so I doubt it can be that. But anyway the data source does not "describe" a driver! It goes on to say "You can use any file data source that refers to an ODBC driver ..." but when I tried setting up a new one it asked for the login details for the specific database so how can I choose "any" ODBC driver based file data source if they are database and login specific! It just doesn't make sense!

Why don't they write these things in clear English, do they have to make it so impossible, is it just to keep network engineers employed?!!

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
GroverParkGeorge
post Jul 17 2017, 05:37 PM
Post#4


UA Admin
Posts: 30,445
Joined: 20-June 02
From: Newcastle, WA


Well, to be honest, it's not easy. I've literally spent days trying to set up a connection on a remote network, so I know it can be frustrating.

On the other hand, it's not really beyond most people if they're willing to invest the time and effort into getting the necessary information.

So, when you say the firewalls are open, you are saying you created rules like this?

Attached File  SQLServerOutFW.png ( 20.16K )Number of downloads: 0


Attached File  SQLServerINFW.png ( 20.68K )Number of downloads: 0


As far as the Select Data Source dialogue is concerned, I'm actually surprised at your characterization.

Of course, you didn't specify where you saw it.... I have to assume it's happening when you create an ODBC connection because that's how you'd connect to SQL Server? Are you going through Access, or ???

There are different ways to get there, so unless you can be more specific about what you are working with, it's just a guess, I'm afraid.

However, given the way you write about it, I might guess you're trying to use an icon on an Access ribbon? More details will help us guide you better. I could offer some thoughts, but it's more productive to see what you're actually doing. Maybe some screen shots?




--------------------
Go to the top of the page
 
GroverParkGeorge
post Jul 17 2017, 11:21 PM
Post#5


UA Admin
Posts: 30,445
Joined: 20-June 02
From: Newcastle, WA


Something else you said just triggered another thought about what to consider:
Attached File  RemoteConnections.png ( 21.44K )Number of downloads: 0


You probably would have done this, but something else to verify.

--------------------
Go to the top of the page
 
firlandsfarm
post Jul 18 2017, 01:19 AM
Post#6



Posts: 246
Joined: 28-April 02
From: Heathfield, England


Hi George ... nope, nothing like that, that is the box I cannot find! As I said I'm using the Windows Firewall and all I can see is ...

[attachment=83143:Firewall_SQL.JPG]

The "Select Data Source" dialogue I am referring to is reached from Access by ...

New Data Source
From Other Sources
ODBC Database
Link to the data source by creating a linked table

... and that takes me to ...

[attachment=83145:SelectDa...Dialogue.JPG]

I just don't understand what "Select the file data source that describes the driver ..." is saying! The data source I am trying to 'select' is \\LAPTAB\FastSSD(C_Drive)\ProformP\database\PRODB.mdf. Following that path in the "Look in:" box doesn't get me anywhere! There is no Browse button to find this mysterious file so I don't even know what I'm looking for (and cannot find anything on the Internet explaining what to look for, articles just say 'fill in the details' or other unhelpful words) and if I click New and select SQL Server Native Client 10.0 (which I understand is for 2008 R2) and save it when prompted as "ProformSQLLink" it confirms ...

[attachment=83146:FileDataSource.JPG]

... and after clicking Finish it goes on to ask (with my responses)

[attachment=83147:ConnectToLAPTAB.JPG]

... and then I get really stuck! ...

[attachment=83148:Authentication.JPG]

... for authentication do I select Windows (seems to be the preferred method) or do I use SQL and whichever do I have to add a new login to the database on LAPTAB? How does SQL on LAPTAB know I, from BIGSAM, am an OK Windows user? If I do have to add a new login do I add it as my ID on BIGSAM, or is it the MSN Account ID that I use to log in on BIGSAM? Too many variables not covered in Internet articles, I assume they assume this is all common knowledge to someone attempting this but if you are not familiar with how Networks work and are administered you don't know what to look for! It's all these 'loose ends', as Columbo would say, that lead to my confusion. smile.gif So let's stick to Windows authentication and click Next and we get ...

[attachment=83149:ConnectionFailed.JPG]

... I've Googled the "Server Error: -2146893042" and ... nothing. Also, it suddenly raises the issue of Credentials, it's telling me something it's missing that it hasn't asked for! (See what I mean about being totally unhelpful.)

Why oh why can't Microsoft and other manual style websites just simply say "This is how you need to set up the enquiring computer and the server computer ..." in a fully explained, complete and clear way rather than leave people surrounded by blind alleys leading to non-solutions! It's so frustrating to read a half page article that makes it seem so simple but then leaves you in a puddle of mud when you find out it's not simple! Thank God for UA in trying to sort out the mess created but not covered by others. smile.gif

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
GroverParkGeorge
post Jul 18 2017, 06:57 AM
Post#7


UA Admin
Posts: 30,445
Joined: 20-June 02
From: Newcastle, WA


Unfortunately, none of your screen shots survived. Can you try again?

--------------------
Go to the top of the page
 
GroverParkGeorge
post Jul 18 2017, 08:56 AM
Post#8


UA Admin
Posts: 30,445
Joined: 20-June 02
From: Newcastle, WA


Okay, here's a brief overview of what you need to do.

First, a bit of background on ODBC and DSNs

ODBC stands for Open DataBase Connectivity. It is a standard API for accessing DBMSs, i.e. it is used by MS Access to access data in SQL Server. It provides a standard set of functions to manipulate and manage the data in the database. There are other protocols. However, ODBC seems to be the most commonly used.

What matters here is that linking tables creates a connection in those tables. That connection is stored--in Access--as a string which tells Access where to find that table. Here's an example from the MSysObjects table in an Access 2016 database (It'll be the same in previous versions).

Attached File  ConnectionStrings.png ( 19.02K )Number of downloads: 1


Sometimes this is referred to as the Connection String because it is a string of text indicating how the source table is connected in a linked table.

The Connect property indicates which Driver I used. In this case I used the oldest one, SQL Server, which has been around for many years. There are newer drivers. The choice of which driver to use is beyond our scope here. I seldom actually use this one in production anymore, though.

The connection also indicates the Server on which the database resides. In this case, it is my laptop, called GPGeorge. Naming of SQL Server instances is another matter. I won't go into it here beyond mentioning a couple of important points.

First, if you installed SQL Server Express, it will be what is called a "Named Instance". By default, when any version of SQL Server is installed, it will be identifiable by the name of the server (or computer) on which it is installed, i.e. "GPGeorge" in my case, or your "LAPTAB".

However, you can install multiple instances of SQL Server on any Server. If you do, subsequent installations must be "Named Instances" to distinguish among them from the initial instance and any other instances installed thereafter. Therefore, I would have, perhaps, "GPGeorge\SS2008" as a second, named instance on the laptop. With SQL Server Express, though ALL instances must be Named Instances. To connect to them, you must include both the server name and the instance name. Let me restate and rephrase that for clarity. I installed SS Standard (a licensed version) on my laptop. That means it is identified as "GPGeorge" when I connect to it from a second computer on my LAN. I can add a second instance, either another licensed version or a SQL Server Express instance. It would have to be identified as a named instance when connecting to it: "GPGeorge\SS2008" or ""GPGeorge\SSE_2016" . Again, only the first installed instance can be identified by the server name.

Key point: Even if there is only one instance, if it is SQL Server Express, that one instance must be identified as a Named Instance.

=======
Now that I think about it, this seems like it could well be the reason for your problem here. If you installed SQL Server Express on the laptop, it will have to be a named instance and you must include that in your connection to it from the new computer: "LAPTAB\YourSSInstanceNameGoesHere"
=======

So, back to the connection string. The connect property of linked tables indicates the UID, or UserID, used to connect to that SQL Server instance, and the PWD, or Password. These are stored in plain text in this table. Not at all secure, for those who know how it works.
The connection string includes

A DSN is a Data Source Name which stores the information needed in a structure on the computer. There is some advantage to that, but some disadvantages. I think that most experienced devs prefer DSN-less connections which store the connection string internally, though.

There's a lot more, but first, see if the named instance issue helps resolve the current problem.

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th September 2017 - 09:40 AM