OLAP Analysis Services - Remote Connection issue

Hi!

does anybody tell me how to connect remotely to an OLAP Analysis Server?

I have defined a local windows account on the OLAP server and added it to AS cube roles.

Despite that I still can't connect remotely to the AS server.

Thanks,

Chris

If this is about connecting with SQL Management Studio (or BI Development Studio) to Analysis Services 2005:

- you need to have a Windows account in the domain of the 2 machines (the AS2005 machine and the client machine from where you want to connect remotely)

- temporarily make the user a server administrator on the AS2005 (to eliminate the potential permission problems); you can do this with SQL Management Studio, right click on the server item -> Properties -> Security

- run SQL Management Studio on the client machine as that user ("Run As..." should be fine, no need to login to Windows as that user)

- if the connection fails, then please check the firewall(s), allow incoming connections for msmdsrv.exe (the AS2005 server)

- if the connection succeeds, then downgrade the user from server administrator; it is enough to grant read definition permission for the database intended; to do this, run SQL Management Studio from an AS2005 admin account and then add the user as a member of the database role

Adrian Dumitrascu

Offline/remote backups

Hi

What is the best way to perform daily offline/remote/off-premise backups of our websistes Sql Server 2005 database?

I've looked into using SSIS but for one reason or another am having serious problems with this. Ideally we'd like to be able connect over the internet and/or lan and pull the entire database down in one go. Our db is ~600mb so isn't major.

We used to use Sql Server 2000's DTS and this worked well, but I'm having a real hard time using SSIS to do the same so was wondering what the other options are

Many thanks

Ben

I'm curious as to why you're going the DTS/SSIS route rather than simply using a standard SQL backup and copying the backup file to a remote location. Understanding that would help us come up with a scenrio that would meet your needs.

If you need a remote system that is a hot or cold standby, you might consider something like log-shipping.

|||

Hi Kevin

The reason we used DTS originally was that it allowed us to backup over the internet. Obviously, upgrading to Sql Server 2005 made SSIS the logical choice.

My customer has two servers running inside the same building and he wants the live database periodically backed-up to the other server so in the event of hard-drive failure, they can still retrieve their data. Also, possibly just once a day, they'd like to download the entire database to another office via the internet.

I suggested to my customer about using the normal backup and possibly FTP'ing the backup file to a remote location. However, log-shipping certianly seems like a good idea. I'd never heard of it until just you mentioned it, so thankyou very much.

Do both instances of Sql Server have to be the same type? Ie, can an Enterprise or Standard version log-ship to a Sql Express instance? Can more than one other instance be used for log-shipping?

Thanks for your help

Ben

|||Log shipping is not supported in SQL Server 2005 Express. If you want, you can emulate log shipping by doing a job that backs up the transaction log on the primary server, FTP the backup to the standby server, and restore the backup on the standby server in read-only mode. This can be done using scripts. A good understanding of how log shipping works will give you an idea on how to create the custom log shipping using a combination of jobs and scripts. The problem with this is that you do not have the luxury of a monitor server nso you need to make sure that you implement your alerts properly. You can check http://www.sql-server-performance.com/sql_server_log_shipping.asp for a reference article on how to do a custom log shipping with scripts, jobs and all.

Odd Server Behavior

I have a new SQL Server installation on a remote dedicated web server server
hosting several websites.
If I use Remote Desktop, I can log in on the server and perform any DB task,
using any of the created logins without any problem. I can use Enterprise
Manager & Query Analyzer without any problems.
But if I connect with Query Analyzer or Enterprise Manager remotely (from my
home or office PC), things start getting really strange.
First, I don't see all the databases when I connect as sa in Query Analyzer
or Enterprise Manager. Now, I don't like using sa remotely in the first
place, so I have an account that has complete admin access to the database
server. And that account works fine on the server. But no other account on
the server will connect to the database remotely except sa. When I do
connect as sa, I don't see all objects on the server. I don't see 1
database. The other 2 databases I do see? I don't see all the objects using
either QA or EM. I can't run select statements on them. If I run SELECT *
FROM SYSOBJECTS remotely, I don't see all the objects. If I run SELECT *
FROM SYSOBJECTS on the server, I see everything. And yes, the accounts are
set up correctly, they all work as configured on the server. And when I
thought it couldn't behave more wierd, it did. I didn't see a table named
ClubCalendar' remotely. So I created it remotely to see what would happen.
And it created the new table. And if I log on the server, I don't see that
table. I know I'm connecting to the correct server because I doubt anyone
else would use the password I'm using for sa and that account does connect.
Anyone have any ideas about this? This is starting to drive me batty.
Dan
Problem solved. I had to shut down the local instance and not start it and
reset the TCP port. Even though it was set to 1433, I had to set it again.
Odd, since this was working yesterday morning.
Dan
"Dan" wrote:

> I have a new SQL Server installation on a remote dedicated web server server
> hosting several websites.
> If I use Remote Desktop, I can log in on the server and perform any DB task,
> using any of the created logins without any problem. I can use Enterprise
> Manager & Query Analyzer without any problems.
> But if I connect with Query Analyzer or Enterprise Manager remotely (from my
> home or office PC), things start getting really strange.
> First, I don't see all the databases when I connect as sa in Query Analyzer
> or Enterprise Manager. Now, I don't like using sa remotely in the first
> place, so I have an account that has complete admin access to the database
> server. And that account works fine on the server. But no other account on
> the server will connect to the database remotely except sa. When I do
> connect as sa, I don't see all objects on the server. I don't see 1
> database. The other 2 databases I do see? I don't see all the objects using
> either QA or EM. I can't run select statements on them. If I run SELECT *
> FROM SYSOBJECTS remotely, I don't see all the objects. If I run SELECT *
> FROM SYSOBJECTS on the server, I see everything. And yes, the accounts are
> set up correctly, they all work as configured on the server. And when I
> thought it couldn't behave more wierd, it did. I didn't see a table named
> ClubCalendar' remotely. So I created it remotely to see what would happen.
> And it created the new table. And if I log on the server, I don't see that
> table. I know I'm connecting to the correct server because I doubt anyone
> else would use the password I'm using for sa and that account does connect.
> Anyone have any ideas about this? This is starting to drive me batty.
> --
> Dan

Odd Server Behavior

I have a new SQL Server installation on a remote dedicated web server server
hosting several websites.
If I use Remote Desktop, I can log in on the server and perform any DB task,
using any of the created logins without any problem. I can use Enterprise
Manager & Query Analyzer without any problems.
But if I connect with Query Analyzer or Enterprise Manager remotely (from my
home or office PC), things start getting really strange.
First, I don't see all the databases when I connect as sa in Query Analyzer
or Enterprise Manager. Now, I don't like using sa remotely in the first
place, so I have an account that has complete admin access to the database
server. And that account works fine on the server. But no other account on
the server will connect to the database remotely except sa. When I do
connect as sa, I don't see all objects on the server. I don't see 1
database. The other 2 databases I do see? I don't see all the objects usin
g
either QA or EM. I can't run select statements on them. If I run SELECT *
FROM SYSOBJECTS remotely, I don't see all the objects. If I run SELECT *
FROM SYSOBJECTS on the server, I see everything. And yes, the accounts are
set up correctly, they all work as configured on the server. And when I
thought it couldn't behave more wierd, it did. I didn't see a table named
ClubCalendar' remotely. So I created it remotely to see what would happen.
And it created the new table. And if I log on the server, I don't see that
table. I know I'm connecting to the correct server because I doubt anyone
else would use the password I'm using for sa and that account does connect.
Anyone have any ideas about this? This is starting to drive me batty.
DanProblem solved. I had to shut down the local instance and not start it and
reset the TCP port. Even though it was set to 1433, I had to set it again.
Odd, since this was working yesterday morning.
Dan
"Dan" wrote:

> I have a new SQL Server installation on a remote dedicated web server serv
er
> hosting several websites.
> If I use Remote Desktop, I can log in on the server and perform any DB tas
k,
> using any of the created logins without any problem. I can use Enterprise
> Manager & Query Analyzer without any problems.
> But if I connect with Query Analyzer or Enterprise Manager remotely (from
my
> home or office PC), things start getting really strange.
> First, I don't see all the databases when I connect as sa in Query Analyze
r
> or Enterprise Manager. Now, I don't like using sa remotely in the first
> place, so I have an account that has complete admin access to the database
> server. And that account works fine on the server. But no other account
on
> the server will connect to the database remotely except sa. When I do
> connect as sa, I don't see all objects on the server. I don't see 1
> database. The other 2 databases I do see? I don't see all the objects us
ing
> either QA or EM. I can't run select statements on them. If I run SELECT
*
> FROM SYSOBJECTS remotely, I don't see all the objects. If I run SELECT *
> FROM SYSOBJECTS on the server, I see everything. And yes, the accounts ar
e
> set up correctly, they all work as configured on the server. And when I
> thought it couldn't behave more wierd, it did. I didn't see a table named
> ClubCalendar' remotely. So I created it remotely to see what would happen
.
> And it created the new table. And if I log on the server, I don't see tha
t
> table. I know I'm connecting to the correct server because I doubt anyone
> else would use the password I'm using for sa and that account does connect
.
> Anyone have any ideas about this? This is starting to drive me batty.
> --
> Dan

Odd FMTONLY statements

Hi,

I recently started using ADO

for connecting to a remote SQL Server from Delphi.

Prior to this I used BDE (Borland Database Engine).

I noticed that all SQL communication seemed slow – only

half the speed of using BDE!

I ran a trace using the Profiler (from MS SQL Server)

and noticed that ADO

generated roughly twice the amount of traffic compared to BDE. Every SQL statement (SELECT,

INSERT and UPDATE) send from my application was preceded by the exact same

statement encapsulated in ‘SET FMTONLY ON/OFF’.

-Example Start-

SET FMTONLY ON SELECT * FROM TabelVersion SET FMTONLY

OFF

SELECT * FROM TabelVersion

-Example End-

Why are these odd statements being passed to the

server?

Shouldn’t the provider be able to handle the returned

dataset without sending the FMTONLY statement in advance? And if not, why doesn’t

it cache this information?

Is it possible to minimize this traffic?

I also spotted quite a lot ‘SET NO_BROWSETABLE ON/OFF’

statements. What is the use for these?

I found this article (http://support.microsoft.com/kb/836830/en-us)

on MSKB, but I should already have the hotfix for this installed by MDAC 2.8 SP1!

This slow pace is killing me – please help - egeskov

---

My configuration:

Workstation:

WinXP SP2

MDAC 2.8 SP1

(2.81.1117.0)

ConnectionString=’Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist

Security Info=False;Application Name=PD:KE;Data Source=SMIL;Initial

Catalog=PDDebug’

Server:

Windows

Server 2000

SP4

MS SQL

Server 8

FMTONLY is an indication that the provider needs parameter or result metadata prior to execution because the application has not made this available. It would help to see some sample code, but essentially you need to avoid things like cmd.Parameters.Refresh and always set the Type property of parameters, etc. If the application doesn't provide all the metadata the provider needs then it costs a round trip to the server to get it. In most cases applications can be written to avoid this and will perform much better as a result.|||

Chris has well explained why the FMTONLY statements come.

My question is that what is the duration you see of these FMTONLY statements in Profiler trace? How is it compared with the duration of the actual select statement? Based on my experience, these FMTONLY statements usually completes instantly and should not cause severe/noticable performance hit.

|||I have made a small code sample that only connects to the server, runs a single query and disconnects:

---

// Procedure that runs then the main form is shown
procedure TForm1.FormShow(Sender: TObject);
var Conn: TADOConnection;
Q: TADOQuery;
begin
// The Connection object is constructed and initialized with the connection string
Conn:=TADOConnection.Create(Nil);
Conn.ConnectionString:='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Application Name=PD:KE;Data Source=Main;Initial Catalog=ProduktData';

// Connecting to the server
Conn.Open;

// The query object is constructed and binded to the connection object
Q:=TADOQuery.Create(Nil);
Q.Connection:=Conn;

// SQL is feed to the query
Q.SQL.Add('SELECT COUNT(*) FROM Param');

// The query is executed
Q.Open;

// The resulting single cell resultset is written to the forms title
Caption:=Q.Fields[0].AsString;

// Objects are destructed
Q.Free;
Conn.Free;
end;

---

My trace from running this sample code is visible here:
http://www.diku.dk/hjemmesider/studerende/egeskov/ADOTrace.trc

I have no clue what parameters my code mught be missing.|||FMTONLY itself should execute fairly quickly at the server and profiler will show you the actual cost. The main concern is the rount trip to the server. This may or may not be significant to your application. It depends if the client and server are on the same system, how scalable the app has to be, how heavily loaded the server is, etc.|||i agree that in this case it's hard to see why the FMTONLY is needed. Maybe Delphi always checks for parameters and somehow is somehow focing this. I don't have access to a copy of Delphi and couldn't find any online documentation so can't investigate further myself. They key thing to try is to supply parameter information before executing the query when the query does have parameters, and check if there is a way to tell Delphi that there are no parameters otherwise.|||

Note the FMTONLY statements will be generated by the ADO client cursor code when it is trying to create the client side cursor.

For example look at ->

http://groups.google.com/group/borland.public.delphi.database.sqlservers/browse_thread/thread/99a3a60f8fefa14f/9f7edb9e622d7cfb%239f7edb9e622d7cfb

My recommendation (note I don't know much about TADOQuery but I culled this from searching around the internet) ->

Switch CursorLocation to clUseServer, CursorType to ctOpenForwardOnly and LockType to ltReadOnly

This should avoid it.

|||Thanks for

your interest.

Due to your

suggestions a got hold of the ADOQuery’s ParamCheck property. By disabling

this, I got rid of the FMTONLY statements which increased the performance by

almost 100 % – the number of round trips really is significant!
I have also

done some fiddling about with the CursorLocation, CursorType and LockType, but

my first attempts didn’t improve performance compared to disabling ParamCheck.

I’ll definitely have to look further into this.|||Nice. I have the same problem. I will try this. Did you find a solution for the NO_BROWSE. I have the same thing. Bill.|||No I haven't really looked

into it jet.

ODBC on remote computer won't access

I'm attempting to connect to an SQL Server Express database from a remote PC, but it isn't connecting. I have a user account created in the database, and I'm using the SQL authentication with that user account. I have success with this if I'm on the server that the database is on, but not remotely. Both are on the same network, even plugged into the same hub. There doesn't appear to be any network problems.

Any ideas? Thanks!

Please follow this instruction to enable remote connections: http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

If you still see problem, follow the instruction here and give us more details http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1

ODBC link across a vpn / WAN / internet link

Is it possible to achieve an ODBC link from a client pc to a remote SQL
server, either over a VPN / WAN or internet connection?
I'm looking to link a access front end to a SQL server, using a hosted
remote server.
TIAYes, it's possible and easy. However, the port 1433 (of the other one if
you set up the SQL-Server to listen to another port) must be open through
all firewalls; something that is easy to do on your side but less likely if
you are using an hosted server. For security purposes, most commercial web
hosting services will refuse to open the port 1433 through their firewall in
order to protect their sql-servers.
For OLEDB, make sure that you specify the TCP/IP protocol by indicating the
Network Library:
Provider=sqloledb;Data Source=190.190.200.100,1433;Network
Library=DBMSSOCN;Initial Catalog=myDataBase;User
ID=myUsername;Password=myPassword;
See: http://www.connectionstrings.com/?carrier=sqlserver or
http://www.carlprothman.net/Technology/ConnectionStrings/ODBCDSNLess/tabid/90/Default.aspx
For further explanations, take a look at the m.p.access.odbcclientsvr,
m.p.a.internet or m.p.a.externaldata newsgroups.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Fred" <fred@.blah.com> wrote in message
news:e7HoX4UQHHA.1364@.TK2MSFTNGP06.phx.gbl...
> Is it possible to achieve an ODBC link from a client pc to a remote SQL
> server, either over a VPN / WAN or internet connection?
> I'm looking to link a access front end to a SQL server, using a hosted
> remote server.
> TIA
>
>

ODBC link across a vpn / WAN / internet link

Is it possible to achieve an ODBC link from a client pc to a remote SQL
server, either over a VPN / WAN or internet connection?
I'm looking to link a access front end to a SQL server, using a hosted
remote server.
TIA
Yes, it's possible and easy. However, the port 1433 (of the other one if
you set up the SQL-Server to listen to another port) must be open through
all firewalls; something that is easy to do on your side but less likely if
you are using an hosted server. For security purposes, most commercial web
hosting services will refuse to open the port 1433 through their firewall in
order to protect their sql-servers.
For OLEDB, make sure that you specify the TCP/IP protocol by indicating the
Network Library:
Provider=sqloledb;Data Source=190.190.200.100,1433;Network
Library=DBMSSOCN;Initial Catalog=myDataBase;User
ID=myUsername;Password=myPassword;
See: http://www.connectionstrings.com/?carrier=sqlserver or
http://www.carlprothman.net/Technology/ConnectionStrings/ODBCDSNLess/tabid/90/Default.aspx
For further explanations, take a look at the m.p.access.odbcclientsvr,
m.p.a.internet or m.p.a.externaldata newsgroups.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Fred" <fred@.blah.com> wrote in message
news:e7HoX4UQHHA.1364@.TK2MSFTNGP06.phx.gbl...
> Is it possible to achieve an ODBC link from a client pc to a remote SQL
> server, either over a VPN / WAN or internet connection?
> I'm looking to link a access front end to a SQL server, using a hosted
> remote server.
> TIA
>
>

ODBC link across a vpn / WAN / internet link

Is it possible to achieve an ODBC link from a client pc to a remote SQL
server, either over a VPN / WAN or internet connection?
I'm looking to link a access front end to a SQL server, using a hosted
remote server.
TIAYes, it's possible and easy. However, the port 1433 (of the other one if
you set up the SQL-Server to listen to another port) must be open through
all firewalls; something that is easy to do on your side but less likely if
you are using an hosted server. For security purposes, most commercial web
hosting services will refuse to open the port 1433 through their firewall in
order to protect their sql-servers.
For OLEDB, make sure that you specify the TCP/IP protocol by indicating the
Network Library:
Provider=sqloledb;Data Source=190.190.200.100,1433;Network
Library=DBMSSOCN;Initial Catalog=myDataBase;User
ID=myUsername;Password=myPassword;
See: http://www.connectionstrings.com/?carrier=sqlserver or
http://www.carlprothman.net/Technol...br />
ult.aspx
For further explanations, take a look at the m.p.access.odbcclientsvr,
m.p.a.internet or m.p.a.externaldata newsgroups.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Fred" <fred@.blah.com> wrote in message
news:e7HoX4UQHHA.1364@.TK2MSFTNGP06.phx.gbl...
> Is it possible to achieve an ODBC link from a client pc to a remote SQL
> server, either over a VPN / WAN or internet connection?
> I'm looking to link a access front end to a SQL server, using a hosted
> remote server.
> TIA
>
>