Microsoft SQL Server forum - Remote access to sql database...permissions

Author

Remote access to sql database...permissions

Phil

2005-04-19, 1:23 pm

Hi all,

I need some help to access an SQL db on another machine. I am using VB.NET
and remoting to make a client/server connection...although I don't think
this is relevant to the question.

I have been asked to help with a small db project that will reside on our
office server and have approx. 5 users.

I have installed a copy of MSDE on my development machine and plan on doing
the same on my office 'server'....in fact it may well already be on there. I
also have another testbed to act as the client.

Because I don't really know what I'm doing...........I have copied the db
created on my client to the server. I have done this by 'cheating'. I use
the VB.NET IDE to create the correctly named db under the sql server. I then
go to this file in windows explorer and write over it (and the .LDF) with
the file from the client.

When I try to connect to this db remotely I get a fail to connect error
about not recognising 'localmachinename\gu
est'...sorry, not the exact error
wording. I can provide this if it will be helpful.

Can anyone point me in the right direct?

I don't have any sql/db tools other than anything that is included with MSDE
(which I don't think is much).

I will be posting other Q's for any experts out there!

Thanks in advance.

Phil

Simon Hayes

2005-04-19, 8:23 pm

"Phil" <Phil@nospam.com> wrote in message
news:d43i0l$eem$1@nw
rdmz01.dmz.ncs.ea.ibs-infra.bt.com...
> Hi all,
>
> I need some help to access an SQL db on another machine. I am using VB.NET
> and remoting to make a client/server connection...although I don't think
> this is relevant to the question.
>
> I have been asked to help with a small db project that will reside on our
> office server and have approx. 5 users.
>
> I have installed a copy of MSDE on my development machine and plan on
> doing the same on my office 'server'....in fact it may well already be on
> there. I also have another testbed to act as the client.
>
> Because I don't really know what I'm doing...........I have copied the db
> created on my client to the server. I have done this by 'cheating'. I use
> the VB.NET IDE to create the correctly named db under the sql server. I
> then go to this file in windows explorer and write over it (and the .LDF)
> with the file from the client.
>
> When I try to connect to this db remotely I get a fail to connect error
> about not recognising 'localmachinename\gu
est'...sorry, not the exact
> error wording. I can provide this if it will be helpful.
>
> Can anyone point me in the right direct?
>
> I don't have any sql/db tools other than anything that is included with
> MSDE (which I don't think is much).
>
> I will be posting other Q's for any experts out there!
>
> Thanks in advance.
>
> Phil
>

I don't know exactly what caused the error (it's always a good idea to
provide the exact error message), however the way you've copied the database
probably isn't very reliable. See my reply to your previous post for more
details on how to copy a database to another server.

You should also consider that as of SP3, MSDE does not accept network
connections by default, so even if the database was copied correctly, you
might still not be able to access it remotely. More details here:

http://support.microsoft.com/defaul...kb;en-us;319930

You can use SVRNETCN.EXE (the SQL Server Network Utility) to enable network
support.

Simon

Phil

2005-04-20, 7:23 am

Hi Simon or anyone else!

I have looked through the information provided by MS Support (from your
link...thanks) and have not been successful in connecting to the remote dB.
The error returned is:-

SQL - Cannot open database requested in login 'TsHelpDesk'. Login fails.

Login failed for user '[ClientMachineName]
\ASPNET'. at
System.Data.SqlClient.ConnectionPool. GetConnection(Boolea
n& isInTransaction)
at
System.Data.SqlClient. SqlConnectionPoolMan
ager. GetPooledConnection(
SqlConnectionString
options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter. QuietOpen(IDbConnect
ion connection,
ConnectionState& originalState)
at System.Data.Common.DbDataAdapter. FillFromCommand(Obje
ct data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
srcTable)
at SQLHelpDesk.HelpSC.FillDb() in
D:\Backup\Work\HelpD
esk\SQLHelpDesk\Help
SC.vb:line 72
at SQLHelpDeskClient.HDeskClient.Page_Load(Object sender, EventArgs e) in
c:\inetpub\wwwroot\S
QLHelpDeskClient\Def
ault.aspx.vb:line 56

I use a standard SQL connection string:-

sqlcnn = New SqlConnection("data source=(local)\NetSD
K;" & _
"initial catalog=TsHelpDesk;i
ntegrated security=SSPI")

I have tried changing the above to providing an authenticated user name, ie.
User ID=username;Password
=;. I've also tried changing the db owner as well
as adding various user accounts to the dbowner using "sp_addrolemember
'dbowner'". None of this has changed the above response from the server, it
is always the same with the exception of the user details where I have
changed them!

Can you assist and further? I hope so!!!

Cheers for now.

Phil

"Simon Hayes" <sql@hayes.ch> wrote in message
news:42655c61$1_2@ne
ws.bluewin.ch...
>
> "Phil" <Phil@nospam.com> wrote in message
> news:d43i0l$eem$1@nw
rdmz01.dmz.ncs.ea.ibs-infra.bt.com...
>
> I don't know exactly what caused the error (it's always a good idea to
> provide the exact error message), however the way you've copied the
> database probably isn't very reliable. See my reply to your previous post
> for more details on how to copy a database to another server.
>
> You should also consider that as of SP3, MSDE does not accept network
> connections by default, so even if the database was copied correctly, you
> might still not be able to access it remotely. More details here:
>
> http://support.microsoft.com/defaul...kb;en-us;319930
>
> You can use SVRNETCN.EXE (the SQL Server Network Utility) to enable
> network support.
>
> Simon
>
>
>

Simon Hayes

2005-04-21, 7:23 am

"Cannot open database requested in login 'TsHelpDesk'" suggests that
either the database doesn't exist, or the login you're using has no
access to it. You say you're connecting to a "remote" database, but the
connection string seems to be local - could that be the issue?

If not, I would check that the login you're using has been granted
access to the database with sp_grantdbaccess.

Simon

Simon Hayes

2005-04-21, 7:23 am

Sponsored Links