MS Access project with SQL Server - Can't setup a logon for a remote computer

Author

Can't setup a logon for a remote computer

robert demo via AccessMonster.com

2005-09-23, 8:25 pm

I'm just starting to use SQL Server as a backend.

I've searched the web, books online and posted in SQL forums with no luck. So
I thought I would try posting here, but realize that my question may be out
of scope.

It's really a simple question. How does one set up logins/users for users on
a different computer than where SQL Server resides.

When I try to setup a user in SQL Server using Enterprise Manager, it doesn't
show me any of the other computers on the network, only the computer that SQL
Server is on??? So how can I add a Windows logon from that other computer to
SQL Server??

Should I be able to see the other computers on the network in the where it
says "List Names From" on the dialog box entitled "SQL Server Login
Properties - New Login? Do I have to setup my Windows XP to recognize the
other domain so that Windows Authentication can be use. The hard drives for
each PC is available to the other PCs and can readily be seen in Windows
Explorer. Is there something else I need to do.

Thanks, this is driving me crazy!

--
Message posted via http://www.webservertalk.com

Philipp Stiefel

2005-09-24, 9:24 am

"robert demo via webservertalk.com" <forum@webservertalk.com> wrote:

> It's really a simple question. How does one set up logins/users for users on
> a different computer than where SQL Server resides.
>
> When I try to setup a user in SQL Server using Enterprise Manager, it doesn't
> show me any of the other computers on the network, only the computer that SQL
> Server is on??? So how can I add a Windows logon from that other computer to
> SQL Server??

Not at all!

You can add only local accounts or accounts from a Domain
the SQL-Server is in. If you are setting up the SQL-Server
in Workgroup without a domaincontroller you're out of luck.

The easiest way do manage logins in a workgroup-scenario is
by using SQL-Server-Authentication. Which means the user
has to login to SQL-Server by supplying username and password.
You may have to enable SQL-Server-Authentication at the
Server-Properties if haven't done this yet.

There is another option which actually is a bit crude but may
be acceptable im some scenarios. You can create a local Windows
account for each user on the server and give these users
permission to access the SQL-Server. As long as the Windows-
Account on the Server and on the Client has the same password
integrated security will work then. This is quite ugly, but it
may better than no integrated security at all.

Cheers
Phil

robert d via AccessMonster.com

2005-09-24, 11:24 am

Phil:

Thanks for the response. I've got it up and running already. Once I
realized that Windows XP home edition doesn't support domains and that for
workgroups you have to use SQL Server authentication, things fell into place.
I created a Guest account in SQL server on computer A and also created an
ODBC DSN in computer B that points to SQL Server on Computer A and uses the
Guest account to logon.

I'm just setting up access from Computer B to A for testing only, so the fact
that I can't use Windows Authentication is not a big deal.

Wow! I am amazed at the speed with which data is being retrieved from SQL
Server and displayed on Computer B. Data is displayed on Computer B almost
as fast as on Computer A (except for very complex retrievals that involve
calculations on thousands of rows from multiple tables). When I was using
Jet on both computers, the response was substanitally slower on Computer B
even for the simplest displays of data. Even though my code is not optimized
for SQL Server (I use DAO instead of ADO), it looks like at least for my
application I won't have to rewrite the code.

Also, I've been refining and developing my application lately on the SQL
Server version (i.e. linked to SQL server tables instead of Jet). I've
noticed that not once have I gotten a corrupted database or the dreaded
"We're sorry for the inconvenience".

I can see now why people prefer SQL Server and I'm becoming a convert myself.

Thanks.

Philipp Stiefel wrote:
>[quoted text clipped - 3 lines]
>
>Not at all!
>
>You can add only local accounts or accounts from a Domain
>the SQL-Server is in. If you are setting up the SQL-Server
>in Workgroup without a domaincontroller you're out of luck.
>
>The easiest way do manage logins in a workgroup-scenario is
>by using SQL-Server-Authentication. Which means the user
>has to login to SQL-Server by supplying username and password.
>You may have to enable SQL-Server-Authentication at the
>Server-Properties if haven't done this yet.
>
>There is another option which actually is a bit crude but may
>be acceptable im some scenarios. You can create a local Windows
>account for each user on the server and give these users
>permission to access the SQL-Server. As long as the Windows-
>Account on the Server and on the Client has the same password
>integrated security will work then. This is quite ugly, but it
>may better than no integrated security at all.
>
>Cheers
>Phil

--
Message posted via http://www.webservertalk.com

Philipp Stiefel

2005-09-26, 9:24 am

"robert d via webservertalk.com" <u6836@uwe> wrote:

> Wow! I am amazed at the speed with which data is being retrieved from SQL
> Server and displayed on Computer B. Data is displayed on Computer B almost
> as fast as on Computer A (except for very complex retrievals that involve
> calculations on thousands of rows from multiple tables). When I was using
> Jet on both computers, the response was substanitally slower on Computer B
> even for the simplest displays of data. Even though my code is not optimized
> for SQL Server (I use DAO instead of ADO), it looks like at least for my
> application I won't have to rewrite the code.

Good to hear that got the thing running!

Just another comment though. - The diffrence in performance
between DAO and ADO is usually barely noticeable. What really
matters is _your_ code to access the data. E.g. if you have
got queries in Access that do use VBA- or Access-Functions or
you select a large table and process every record with VBA,
you'll certainly have performance issues there and should
rather rewrite that part of the application to run on the
SQL-Server.

Cheers
Phil

Robert Morley

2005-09-26, 11:24 am

> Wow! I am amazed at the speed with which data is being retrieved from SQL
> Server and displayed on Computer B. Data is displayed on Computer B
> almost
> as fast as on Computer A (except for very complex retrievals that involve
> calculations on thousands of rows from multiple tables). When I was using
> Jet on both computers, the response was substanitally slower on Computer B
> even for the simplest displays of data. Even though my code is not
> optimized
> for SQL Server (I use DAO instead of ADO), it looks like at least for my
> application I won't have to rewrite the code.

The reason it's so fast is because using Jet, you had to open the entire
file over the network, send every record/index over the network, even the
ones that were filtered out in the end result, etc. With SQL Server, all
you're sending is the command, then Computer A does all the work locally and
just sends the pertinent results back to Computer B. If you *really* want
to see a speed difference, try Jet vs. SQL Server over a low-speed
connection. I don't recommend a modem, cuz that's a little TOO slow, but
try something like a DSL connection...SQL Server still remains fast, where
Jet becomes a nightmare.

Rob

robert d via AccessMonster.com

2005-09-26, 1:25 pm

Rob:

Is this true even if there the SQL does not reside on Computer A in, what I
think they call, Views. All of my SQL is on the front end and nothing is
either in the Jet backend or SQL SERVER backend. Obviously, I'm seeing the
speed in my case, but according to the books I've been reading, I haven't
optimized SQL Server performance (by using ADO and Views on the Server).
Interesting post by Phil, that there doesn't seem to be a big difference in
speed, at least, between DAO and ADO code.

Thanks.

Robert Morley wrote:
>[quoted text clipped - 6 lines]
>
>The reason it's so fast is because using Jet, you had to open the entire
>file over the network, send every record/index over the network, even the
>ones that were filtered out in the end result, etc. With SQL Server, all
>you're sending is the command, then Computer A does all the work locally and
>just sends the pertinent results back to Computer B. If you *really* want
>to see a speed difference, try Jet vs. SQL Server over a low-speed
>connection. I don't recommend a modem, cuz that's a little TOO slow, but
>try something like a DSL connection...SQL Server still remains fast, where
>Jet becomes a nightmare.
>
>Rob

--
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Fo...server/200509/1

Robert Morley

2005-09-26, 1:25 pm

While putting the views on the back end would make it faster still, it's the
data we're most concerned about in this instance. (Like Jet, SQL Server can
pre-optimize views that are stored locally; it can't optimize ad-hoc
commands until run-time and it has to re-optimize them every time the
command is sent.)

If, for example, you've got a query that returns 5 out of 1000 records, for
Jet, it has nothing running on Computer A, so it's forced to fetch all 1000
records and return them for examination; for SQL Server, even though the
query/view is on the front end, chances are it's SQL Server that's still
doing the bulk of the hard work.

DAO is minimally aware of SQL Server and can send most common queries
directly to SQL Server for processing, so if your query is something like
"SELECT * FROM MyTable WHERE Something = 5" then SQL Server's doing the
work, not DAO, and you only get one command sent, then the 5 records
returned.

If, on the other hand, your query is just "SELECT * FROM MyTable" (or
opening the table directly) and then you go through it to find the instances
of Something = 5, that's a different story. Even in that instance, though,
you may see something of a savings, since you don't have all kinds of
meta-data going back and forth over the network, you JUST have the records
themselves being transmitted.

One of the "gotcha's" here is if you have a query that does something like
this: "SELECT * FROM MyTable WHERE Nz(Something) = 5", then DAO's still
doing the bulk of the work. Because Nz is an Access function that SQL
Server doesn't recognize, it sends "SELECT * FROM MyTable" to SQL Server,
than parses the Nz() on its side on all 1000 records.

Rob

"robert d via webservertalk.com" <u6836@uwe> wrote in message
news:54f3546f432e0@u
we...
> Rob:
>
> Is this true even if there the SQL does not reside on Computer A in, what
> I
> think they call, Views. All of my SQL is on the front end and nothing is
> either in the Jet backend or SQL SERVER backend. Obviously, I'm seeing
> the
> speed in my case, but according to the books I've been reading, I haven't
> optimized SQL Server performance (by using ADO and Views on the Server).
> Interesting post by Phil, that there doesn't seem to be a big difference
> in
> speed, at least, between DAO and ADO code.
>
> Thanks.

Philipp Stiefel

2005-09-27, 3:24 am

"Robert Morley" <rmorley@magma.ca.no.freakin.spam> wrote:

[...]
> One of the "gotcha's" here is if you have a query that does something like
> this: "SELECT * FROM MyTable WHERE Nz(Something) = 5", then DAO's still
> doing the bulk of the work. Because Nz is an Access function that SQL
> Server doesn't recognize, it sends "SELECT * FROM MyTable" to SQL Server,
> than parses the Nz() on its side on all 1000 records.

Good explanation!

But there is one inaccuracy, that should be clarified a bit.
DAO (as well as ADO) is just a data access technology and
therefore does not do any work itself, but communicates with
database engines that does the real work. Any query that you
perform on a table in an Access-Database (MDB), regardless
which data access technology you use, is first processed by
the Jet-Engine. If the Jet engine reckons the table involved
is a linked table from SQL-Server it passes the query on to
the server for processing, unless the statement contains a
function (Rob's example) that has to be processed by the Jet-
Expression-Service. In this case the part of the statement
containing the function is stripped out and only the rest
of the statement is passed to the server. The part of the
statement including the functioncall will then be processed
locally on the result returned by the server. Everything is
quite as Rob explained, but it has nothing to do with DAO.

Cheers
Phil

Robert Morley

2005-09-27, 8:25 pm

> Good explanation!
>
> But there is one inaccuracy, that should be clarified a bit.
> DAO (as well as ADO) is just a data access technology and
> therefore does not do any work itself, but communicates with
> database engines that does the real work.

My bad. I thought it was DAO that figured out whether it should send to Jet
or to SQL Server, but I suppose that wouldn't make much sense in the end.
It's so much more obvious with ADO (well, unless you're accessing linked
tables, in which case you're in the same scenario).

Rob

MIGUEL MIRANDA

2005-10-27, 8:32 am

robert demo via webservertalk.com <forum@webservertalk.com> wrote in message
news:54D1CFCCC2020@w
ebservertalk.com...
> I'm just starting to use SQL Server as a backend.
>
> I've searched the web, books online and posted in SQL forums with no luck.
So
> I thought I would try posting here, but realize that my question may be
out
> of scope.
>
> It's really a simple question. How does one set up logins/users for users
on
> a different computer than where SQL Server resides.
>
> When I try to setup a user in SQL Server using Enterprise Manager, it
doesn't
> show me any of the other computers on the network, only the computer that
SQL
> Server is on??? So how can I add a Windows logon from that other computer
to
> SQL Server??
>
> Should I be able to see the other computers on the network in the where it
> says "List Names From" on the dialog box entitled "SQL Server Login
> Properties - New Login? Do I have to setup my Windows XP to recognize the
> other domain so that Windows Authentication can be use. The hard drives
for
> each PC is available to the other PCs and can readily be seen in Windows
> Explorer. Is there something else I need to do.
>
> Thanks, this is driving me crazy!
>
>
> --
> Message posted via http://www.webservertalk.com

noe

2005-10-27, 8:32 am

jjjjjjjjj
"robert demo via webservertalk.com" <forum@webservertalk.com> escribió en el
mensaje news:54D1CFCCC2020@w
ebservertalk.com...
> I'm just starting to use SQL Server as a backend.
>
> I've searched the web, books online and posted in SQL forums with no luck.
So
> I thought I would try posting here, but realize that my question may be
out
> of scope.
>
> It's really a simple question. How does one set up logins/users for users
on
> a different computer than where SQL Server resides.
>
> When I try to setup a user in SQL Server using Enterprise Manager, it
doesn't
> show me any of the other computers on the network, only the computer that
SQL
> Server is on??? So how can I add a Windows logon from that other computer
to
> SQL Server??
>
> Should I be able to see the other computers on the network in the where it
> says "List Names From" on the dialog box entitled "SQL Server Login
> Properties - New Login? Do I have to setup my Windows XP to recognize the
> other domain so that Windows Authentication can be use. The hard drives
for
> each PC is available to the other PCs and can readily be seen in Windows
> Explorer. Is there something else I need to do.
>
> Thanks, this is driving me crazy!
>
>
> --
> Message posted via http://www.webservertalk.com

Sponsored Links