Prev: SP3 Install instructions
Next: Unable to shrink trans log due to log_reuse_wait_desc=REPLICATION
From: mat on 25 Jan 2009 11:04
I'd guess this is doable but that I just have not found the right config
to make it work. I vpn into a remote corporate network (cisco vpn
software) and via RD use an installation of sql server 2005 mamagement
studio on that box to work with both a sql server instance installed on
that box, and the production db on another box on the company AD
network. I have local admin rights on the box that I dial in to and dba
rights on the prod server. I would really like to be able to connect to
the databases from my local copy of sql server 2005 management studio
but have never been able to get it to work.

Here is how I try to connect.

I fire up the vpn and log in. My credentails with the vpn are myname and
mypassword. The cisco vpn hostname is like endpoint.somecorp.com.

On the copy of sql server 2005 management studio that is installed on
the remote box that I can RD into, the two sql server instances are
available to me as

mssqlsrv03.somecorp.com (that's production)

sqldev01 (testing server)

I've tried many combinations but nothing 'takes'. Does the remote sql
server need to be configured to allow connections from outside the
corporate domain, or something of that order?
From: newscorrespondent on 25 Jan 2009 12:06
What error message are you getting?

What you want to do is a very common thing to get done.

I suspect there is a permissions problem somewhere along the line.
From: mat on 25 Jan 2009 14:35
In article <#PDDE9wfJHA.1288(a)TK2MSFTNGP02.phx.gbl>,
newscorrespondent(a)charter.net says...
> What error message are you getting?
>
> What you want to do is a very common thing to get done.
>
> I suspect there is a permissions problem somewhere along the line.
>
You're probably right, but I couldn't figure it out and didn't get input
that helped solve it in the original post here.

If I use the database restore option and point to the .bak file, this is
the error I get

Restore failed for Server 'mybox\SQLEXPRESS'.
(Microsoft.SqlServer.Express.Smo)

ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The operating system returned the error
'5(Access is denied.)' while attempting
'RestoreContainer::ValidateTargetForCreation' on 'D:\sqlserver\MSSQL.1
\MSSQL\mydbtest.mdf'. (Microsoft.SqlServer.Express.Smo)

If I use the restore files and filegroups option I get this error:

Restore failed for Server 'mybox\SQLEXPRESS'.
(Microsoft.SqlServer.Express.Smo)

ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.Express.ConnectionInfo)
The file or filegroup "mydb_log" cannot be selected for this operation.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server,
Error: 3219)
From: "Sylvain Lafontaine" sylvain aei ca on 25 Jan 2009 14:51
First, verify that mssqlsrv03.somecorp.com and sqldev01 are not aliases;
second, make sure that you are try to connect using the TCP/IP protocol by
using their tcp/ip addresses, by adding the prefix tcp: , by adding the name
of the instance if they are not unnamed instances and by adding the port
number. For example:

tcp:sqldev01\NameOfTheInstance,1433

Notice that the best way to try these combinations is to create an alias on
your computer. There is also the question of the login account to be used
to connect to the SQL-Server. If your local machine is not part of a
trusted domain or if that domain has not been registered as a valid login
account on the sql-server, you won't be able to connect using your Windows
account. In these case, you'll need to connect using a sql-server account.

If nothing work, then you should speak with the guy in charge of the
network; at it's quite possible that the Cisco network is not configured to
allow an external connection (you) directly with these two sql-servers.

Don't forget that the capability of connecting to a remote sql-server from a
local machine is a security risk; even when using a VPN.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"mat" <mat(a)notarealdotcom.adr> wrote in message
news:MPG.23e62d55a43f95f9989773(a)msnews.microsoft.com...
> I'd guess this is doable but that I just have not found the right config
> to make it work. I vpn into a remote corporate network (cisco vpn
> software) and via RD use an installation of sql server 2005 mamagement
> studio on that box to work with both a sql server instance installed on
> that box, and the production db on another box on the company AD
> network. I have local admin rights on the box that I dial in to and dba
> rights on the prod server. I would really like to be able to connect to
> the databases from my local copy of sql server 2005 management studio
> but have never been able to get it to work.
>
> Here is how I try to connect.
>
> I fire up the vpn and log in. My credentails with the vpn are myname and
> mypassword. The cisco vpn hostname is like endpoint.somecorp.com.
>
> On the copy of sql server 2005 management studio that is installed on
> the remote box that I can RD into, the two sql server instances are
> available to me as
>
> mssqlsrv03.somecorp.com (that's production)
>
> sqldev01 (testing server)
>
> I've tried many combinations but nothing 'takes'. Does the remote sql
> server need to be configured to allow connections from outside the
> corporate domain, or something of that order?


From: Tibor Karaszi on 26 Jan 2009 07:29
> You're probably right, but I couldn't figure it out and didn't get
> input
> that helped solve it in the original post here.

The error message is very clear: The service account doesn't have
permissions to put the database files where you request (per default
same path as originating database). There is no ambiguity for the
operating system error 5. Use the MOVE option for the RESTORE command
if needed.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"mat" <mat(a)notarealdotcom.adr> wrote in message
news:MPG.23e65ee2f008fa0989774(a)msnews.microsoft.com...
> In article <#PDDE9wfJHA.1288(a)TK2MSFTNGP02.phx.gbl>,
> newscorrespondent(a)charter.net says...
>> What error message are you getting?
>>
>> What you want to do is a very common thing to get done.
>>
>> I suspect there is a permissions problem somewhere along the line.
>>
> You're probably right, but I couldn't figure it out and didn't get
> input
> that helped solve it in the original post here.
>
> If I use the database restore option and point to the .bak file,
> this is
> the error I get
>
> Restore failed for Server 'mybox\SQLEXPRESS'.
> (Microsoft.SqlServer.Express.Smo)
>
> ADDITIONAL INFORMATION:
> System.Data.SqlClient.SqlError: The operating system returned the
> error
> '5(Access is denied.)' while attempting
> 'RestoreContainer::ValidateTargetForCreation' on
> 'D:\sqlserver\MSSQL.1
> \MSSQL\mydbtest.mdf'. (Microsoft.SqlServer.Express.Smo)
>
> If I use the restore files and filegroups option I get this error:
>
> Restore failed for Server 'mybox\SQLEXPRESS'.
> (Microsoft.SqlServer.Express.Smo)
>
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or
> batch.
> (Microsoft.SqlServer.Express.ConnectionInfo)
> The file or filegroup "mydb_log" cannot be selected for this
> operation.
> RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server,
> Error: 3219)


 |  Next  |  Last
Pages: 1 2
Prev: SP3 Install instructions
Next: Unable to shrink trans log due to log_reuse_wait_desc=REPLICATION