Best practices for remote users

Hi,
What is the best practice to allow remote users that are not part of our
domain to connect to a specific database on our server? Do we use Windows
Authentication by adding them to the domain or do we use sql authentication.
These users are in a different company and they will be logged on into their
active directory.
I would love to use windows authentication by adding them into our domain
but when setting up ODBC in the control panel, there is no option to send the
user credentials. ODBC seems to want to use the credentials of the currently
logged on user.
These users will be using Microsoft Access to run reports.
Thanks.
Your problem is the users are not logging on to your domain when they start
their machines. I doubt you can get Access to set up a connection using a
separate Windows Account - your best bet is to go with a SQL login - or you
could investigate using a VPN connection to see if that works...
"STech" <STech@.discussions.microsoft.com> wrote in message
news:57BAC952-CCBD-475D-AAD9-F81D8A696D2B@.microsoft.com...
> Hi,
> What is the best practice to allow remote users that are not part of our
> domain to connect to a specific database on our server? Do we use Windows
> Authentication by adding them to the domain or do we use sql
authentication.
> These users are in a different company and they will be logged on into
their
> active directory.
> I would love to use windows authentication by adding them into our domain
> but when setting up ODBC in the control panel, there is no option to send
the
> user credentials. ODBC seems to want to use the credentials of the
currently
> logged on user.
> These users will be using Microsoft Access to run reports.
> Thanks.
|||Hi STech,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Best Practice to update SQL Server Database Tables & Procedures

We will going to have no remote access to the SQL Server 2005â?¦ we as
developers can build and test in our place.
After successfully testing the codesâ?¦ web pages and SQL Server Database new
tables and stored procedures has to be carried physically in the USB Flash
disk and required to go to the host company server location and update the
web pages and SQL Server 2005 Database tables.
What is the best practice â?¦ if the situation is that we have to carry the
table and its data inside table physically to the host location and login to
the server and connect USB flash drive and update tables in SQL Server..
What is the best practice to perform update by going physical to the host
company for make SQL Server 2005 Database changes?> What is the best practice â?¦ if the situation is that we have to carry the
> table and its data inside table physically to the host location and login
> to
> the server and connect USB flash drive and update tables in SQL Server..
> What is the best practice to perform update by going physical to the host
> company for make SQL Server 2005 Database changes?
In both cases, the normal approach is to perform new installations and
upgrades using SQL scripts. You can use a tool like SQLCMD to execute the
scripts from a command file. For upgrades, it is important to test against
a production database replica to ensure the database is properly upgraded.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"TalalSaleem" <TalalSaleem@.discussions.microsoft.com> wrote in message
news:373C5072-E4D4-4F7C-A827-266DAA35C4E4@.microsoft.com...
> We will going to have no remote access to the SQL Server 2005â?¦ we as
> developers can build and test in our place.
> After successfully testing the codesâ?¦ web pages and SQL Server Database
> new
> tables and stored procedures has to be carried physically in the USB Flash
> disk and required to go to the host company server location and update the
> web pages and SQL Server 2005 Database tables.
> What is the best practice â?¦ if the situation is that we have to carry the
> table and its data inside table physically to the host location and login
> to
> the server and connect USB flash drive and update tables in SQL Server..
> What is the best practice to perform update by going physical to the host
> company for make SQL Server 2005 Database changes?|||On Tue, 15 Jan 2008 07:04:25 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:
>> What is the best practice ? if the situation is that we have to carry the
>> table and its data inside table physically to the host location and login
>> to
>> the server and connect USB flash drive and update tables in SQL Server..
>> What is the best practice to perform update by going physical to the host
>> company for make SQL Server 2005 Database changes?
>In both cases, the normal approach is to perform new installations and
>upgrades using SQL scripts. You can use a tool like SQLCMD to execute the
>scripts from a command file. For upgrades, it is important to test against
>a production database replica to ensure the database is properly upgraded.
I think he's asking more about data. Say you need to send someone 1gb
of data, to populate a table, to update a database, etc.
I'd say you can use a good old ASCII CSV or flat file, but of course
you need some kind of import logic, typically some staging tables and
an SSIS package, to do the work.
Josh

Best Practice to update SQL Server Database Tables & Procedures

We will going to have no remote access to the SQL Server 2005… we as
developers can build and test in our place.
After successfully testing the codes… web pages and SQL Server Database new
tables and stored procedures has to be carried physically in the USB Flash
disk and required to go to the host company server location and update the
web pages and SQL Server 2005 Database tables.
What is the best practice … if the situation is that we have to carry the
table and its data inside table physically to the host location and login to
the server and connect USB flash drive and update tables in SQL Server..
What is the best practice to perform update by going physical to the host
company for make SQL Server 2005 Database changes?
> What is the best practice … if the situation is that we have to carry the
> table and its data inside table physically to the host location and login
> to
> the server and connect USB flash drive and update tables in SQL Server..
> What is the best practice to perform update by going physical to the host
> company for make SQL Server 2005 Database changes?
In both cases, the normal approach is to perform new installations and
upgrades using SQL scripts. You can use a tool like SQLCMD to execute the
scripts from a command file. For upgrades, it is important to test against
a production database replica to ensure the database is properly upgraded.
Hope this helps.
Dan Guzman
SQL Server MVP
"TalalSaleem" <TalalSaleem@.discussions.microsoft.com> wrote in message
news:373C5072-E4D4-4F7C-A827-266DAA35C4E4@.microsoft.com...
> We will going to have no remote access to the SQL Server 2005… we as
> developers can build and test in our place.
> After successfully testing the codes… web pages and SQL Server Database
> new
> tables and stored procedures has to be carried physically in the USB Flash
> disk and required to go to the host company server location and update the
> web pages and SQL Server 2005 Database tables.
> What is the best practice … if the situation is that we have to carry the
> table and its data inside table physically to the host location and login
> to
> the server and connect USB flash drive and update tables in SQL Server..
> What is the best practice to perform update by going physical to the host
> company for make SQL Server 2005 Database changes?
|||On Tue, 15 Jan 2008 07:04:25 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:

>In both cases, the normal approach is to perform new installations and
>upgrades using SQL scripts. You can use a tool like SQLCMD to execute the
>scripts from a command file. For upgrades, it is important to test against
>a production database replica to ensure the database is properly upgraded.
I think he's asking more about data. Say you need to send someone 1gb
of data, to populate a table, to update a database, etc.
I'd say you can use a good old ASCII CSV or flat file, but of course
you need some kind of import logic, typically some staging tables and
an SSIS package, to do the work.
Josh

Best Practice Question (clustering and remote locations access)

We have a SQL server here in Portland, we have a new office in Philadelphia
and we need to have both locations working off the same live data.
Regardless we will be clustering the SQL server here, but if we have a fibre
line (either will be DS3 45MB or OC3 155MB) connecting the two sites what
would be the best thing to do, have just the cluster here and phili works
off of that, or add another SQL server to the phili office and have it sync
with the ones here?
Just wondering pros and cons of both, what would be faster & more efficient
etc and wanted to get the opinions of some folks here. I'm not real
concerned about redundancy or uptime at this point, we will tackle that once
we get this first question decided on.
Thanks!
Scott
Hi
If your comms are reliable and have the capacity and response times then
maintaining the single cluster will be the simplest solutions.
Having an internet/intranet based application would be an alternative,
similar a citrix type solution may be another option to consider.
John
"Scott McDonald" wrote:

> We have a SQL server here in Portland, we have a new office in Philadelphia
> and we need to have both locations working off the same live data.
> Regardless we will be clustering the SQL server here, but if we have a fibre
> line (either will be DS3 45MB or OC3 155MB) connecting the two sites what
> would be the best thing to do, have just the cluster here and phili works
> off of that, or add another SQL server to the phili office and have it sync
> with the ones here?
> Just wondering pros and cons of both, what would be faster & more efficient
> etc and wanted to get the opinions of some folks here. I'm not real
> concerned about redundancy or uptime at this point, we will tackle that once
> we get this first question decided on.
> Thanks!
> Scott
>
>
|||this might be a situation in which you would consider Merge Replication.
Not sure, it's a tough question.
Greg Jackson
PDX, Oregon

Best Practice Question (clustering and remote locations access)

We have a SQL server here in Portland, we have a new office in Philadelphia
and we need to have both locations working off the same live data.
Regardless we will be clustering the SQL server here, but if we have a fibre
line (either will be DS3 45MB or OC3 155MB) connecting the two sites what
would be the best thing to do, have just the cluster here and phili works
off of that, or add another SQL server to the phili office and have it sync
with the ones here?
Just wondering pros and cons of both, what would be faster & more efficient
etc and wanted to get the opinions of some folks here. I'm not real
concerned about redundancy or uptime at this point, we will tackle that once
we get this first question decided on.
Thanks!
Scott
Hi
If your comms are reliable and have the capacity and response times then
maintaining the single cluster will be the simplest solutions.
Having an internet/intranet based application would be an alternative,
similar a citrix type solution may be another option to consider.
John
"Scott McDonald" wrote:

> We have a SQL server here in Portland, we have a new office in Philadelphia
> and we need to have both locations working off the same live data.
> Regardless we will be clustering the SQL server here, but if we have a fibre
> line (either will be DS3 45MB or OC3 155MB) connecting the two sites what
> would be the best thing to do, have just the cluster here and phili works
> off of that, or add another SQL server to the phili office and have it sync
> with the ones here?
> Just wondering pros and cons of both, what would be faster & more efficient
> etc and wanted to get the opinions of some folks here. I'm not real
> concerned about redundancy or uptime at this point, we will tackle that once
> we get this first question decided on.
> Thanks!
> Scott
>
>
|||this might be a situation in which you would consider Merge Replication.
Not sure, it's a tough question.
Greg Jackson
PDX, Oregon

Best Practice Question (clustering and remote locations access)

We have a SQL server here in Portland, we have a new office in Philadelphia
and we need to have both locations working off the same live data.
Regardless we will be clustering the SQL server here, but if we have a fibre
line (either will be DS3 45MB or OC3 155MB) connecting the two sites what
would be the best thing to do, have just the cluster here and phili works
off of that, or add another SQL server to the phili office and have it sync
with the ones here?
Just wondering pros and cons of both, what would be faster & more efficient
etc and wanted to get the opinions of some folks here. I'm not real
concerned about redundancy or uptime at this point, we will tackle that once
we get this first question decided on.
Thanks!
Scott
Hi
If your comms are reliable and have the capacity and response times then
maintaining the single cluster will be the simplest solutions.
Having an internet/intranet based application would be an alternative,
similar a citrix type solution may be another option to consider.
John
"Scott McDonald" wrote:

> We have a SQL server here in Portland, we have a new office in Philadelphia
> and we need to have both locations working off the same live data.
> Regardless we will be clustering the SQL server here, but if we have a fibre
> line (either will be DS3 45MB or OC3 155MB) connecting the two sites what
> would be the best thing to do, have just the cluster here and phili works
> off of that, or add another SQL server to the phili office and have it sync
> with the ones here?
> Just wondering pros and cons of both, what would be faster & more efficient
> etc and wanted to get the opinions of some folks here. I'm not real
> concerned about redundancy or uptime at this point, we will tackle that once
> we get this first question decided on.
> Thanks!
> Scott
>
>
|||this might be a situation in which you would consider Merge Replication.
Not sure, it's a tough question.
Greg Jackson
PDX, Oregon

Best Practice Question (clustering and remote locations access)

We have a SQL server here in Portland, we have a new office in Philadelphia
and we need to have both locations working off the same live data.
Regardless we will be clustering the SQL server here, but if we have a fibre
line (either will be DS3 45MB or OC3 155MB) connecting the two sites what
would be the best thing to do, have just the cluster here and phili works
off of that, or add another SQL server to the phili office and have it sync
with the ones here?
Just wondering pros and cons of both, what would be faster & more efficient
etc and wanted to get the opinions of some folks here. I'm not real
concerned about redundancy or uptime at this point, we will tackle that once
we get this first question decided on.
Thanks!
ScottHi
If your comms are reliable and have the capacity and response times then
maintaining the single cluster will be the simplest solutions.
Having an internet/intranet based application would be an alternative,
similar a citrix type solution may be another option to consider.
John
"Scott McDonald" wrote:

> We have a SQL server here in Portland, we have a new office in Philadelphi
a
> and we need to have both locations working off the same live data.
> Regardless we will be clustering the SQL server here, but if we have a fib
re
> line (either will be DS3 45MB or OC3 155MB) connecting the two sites what
> would be the best thing to do, have just the cluster here and phili works
> off of that, or add another SQL server to the phili office and have it syn
c
> with the ones here?
> Just wondering pros and cons of both, what would be faster & more efficien
t
> etc and wanted to get the opinions of some folks here. I'm not real
> concerned about redundancy or uptime at this point, we will tackle that on
ce
> we get this first question decided on.
> Thanks!
> Scott
>
>|||this might be a situation in which you would consider Merge Replication.
Not sure, it's a tough question.
Greg Jackson
PDX, Oregon

Best Practice Question (clustering and remote locations access)

We have a SQL server here in Portland, we have a new office in Philadelphia
and we need to have both locations working off the same live data.
Regardless we will be clustering the SQL server here, but if we have a fibre
line (either will be DS3 45MB or OC3 155MB) connecting the two sites what
would be the best thing to do, have just the cluster here and phili works
off of that, or add another SQL server to the phili office and have it sync
with the ones here?
Just wondering pros and cons of both, what would be faster & more efficient
etc and wanted to get the opinions of some folks here. I'm not real
concerned about redundancy or uptime at this point, we will tackle that once
we get this first question decided on.
Thanks!
Scottthis might be a situation in which you would consider Merge Replication.
Not sure, it's a tough question.
Greg Jackson
PDX, Oregon

Best practice for remote queries

Hi
Would it still be currently regarded as best practice to use OPENQUERY
to run remote queries?
Regards
RichardIf you have a linked server defined you can use
SELECT <> FROM Server.DataBase.dbo.Table
<richhollis@.gmail.com> wrote in message
news:1133219788.491783.204870@.g49g2000cwa.googlegroups.com...
> Hi
> Would it still be currently regarded as best practice to use OPENQUERY
> to run remote queries?
> Regards
> Richard
>

Best approach to updating remote server?

I have a local production server, and a web server at a remote ISP connected
via a VPN. Both servers are running MS SQL 2000. I wish to do an extract
of data on the production server and update it to the web server daily. The
web server only gets a subset of the information on the production server.
I know I can't be the first to need this so what I am searching for is the
best way to accomplish this. Do I refresh the web server daily (about 500MB
of data)? Should I send each table with an indicator as to
Add/Change/Delete, and write a procedure to import? or is there just another
solution I am overlooking.
Any suggestions would be appreciated.
TIA, MarkHi Mark.
A common approach to achieving this is to create a DTS package that
encapsulates the process, applies business rules to the data etc & then
schedule it to run from the local production server. I've done this with
various ISP hosted SQL databases with fairly good success.
Is your ISP hosted SQL Server dedicated or shared? If dedicated, many
options may open up to you that a shared server might have available.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Mark Butler" <mredhat_nospam@.yahoo.com> wrote in message
news:enU%238d8DEHA.2932@.tk2msftngp13.phx.gbl...
> I have a local production server, and a web server at a remote ISP
connected
> via a VPN. Both servers are running MS SQL 2000. I wish to do an extract
> of data on the production server and update it to the web server daily.
The
> web server only gets a subset of the information on the production server.
> I know I can't be the first to need this so what I am searching for is the
> best way to accomplish this. Do I refresh the web server daily (about
500MB
> of data)? Should I send each table with an indicator as to
> Add/Change/Delete, and write a procedure to import? or is there just
another
> solution I am overlooking.
> Any suggestions would be appreciated.
> TIA, Mark
>

Behaviour issue with remote inserts

I have an issue doing remote inserts and don't understand why there is a behaviour difference between local and remote inserts. So far i have not been able to find an

answer. It may be something to do with parametised query execution

but i`m not sure yet.

Below is the scenario

If i do a

insert into server.db.dbo.remotetable

select * from dbo.localtable

and the local select returns say 3 values, 3 inserts will occur on the

destination server where as if the insert into is local only 1 insert would occur!

Why? Is it possible to get the remote query to behave like a local and do the 3 records in 1 insert? Its currently playing havoc with a trigger i have on a production box.

To test this i've supplied some very simple code. Setup instructions are commented into the code. Have not coded a linked server creation though.

At the end look at the tbllog and you will see what i mean.

All advise gratefully received!

Cheers
Andrew

Sample Code

---

--CREATE this table on Source server and Destination server

CREATE TABLE [dbo].[Input] (
[server] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[dt] [datetime] NULL
) ON [PRIMARY]
GO

--Create these on the destination server
CREATE TABLE [dbo].[tbllog] (
[Server] [char] (100) COLLATE Latin1_General_CI_AS NULL ,
[tst_Count] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[destination] (
[server] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[dt] [datetime] NULL
) ON [PRIMARY]
GO

--Create this table on the destination table on destination server
CREATE TRIGGER [destination_ins] ON [dbo].[destination]
FOR INSERT, UPDATE, DELETE
AS
insert into dbo.tbllog
select server,count(server) from inserted group by server
GO

--Insert some sample data into the input table on Source server
insert into input values ('Remote','1/1/2000')
insert into input values ('Remote','1/2/2000')
insert into input values ('Remote','1/3/2000')

--Insert some sample data into the input table on Destination server
insert into input values ('Local','1/1/2000')
insert into input values ('Local','1/2/2000')
insert into input values ('Local','1/3/2000')

--Run the insert from the source server
insert into destinationsrv.destdb.dbo.destination
select * from input

--Run the insert from the destination server (as a local query)
insert into dbo.destination
select * from input

--On the destination server, run select from the log table which was populated by trigger.
--You will see remote insert has 3 rows but local only has 1!
select * from tbllogHmmm. Looks like SQL Server uses cursors to run any remote query, even if it is run via OPENROWSET (just tested it). I wonder if there is a way to avoid this, except moronic workarounds like temp table & remote SP.|||I've been hunting high and low for a way to get it to behave the same way as a local insert and not do multiple inserts without resorting to temp tables and remote sp's. Was hoping there might even have been a reg setting but not found one.

I'd also like to understand why it has to break down into multiple inserts!

Still looking for the answer but no joy. |||

I'm dealing with the same problem. Has anyone found a solution? I'd hate to resort to calling a remote stored procedure just to pull data across the link.

Surely people have encountered this problem before - I'm able to reproduce it in both Sql Server 2000 and 2005. Or do people normally only use linkedservers for queries?