By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,930 Members | 1,269 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,930 IT Pros & Developers. It's quick & easy.

Remote access to sql database...permissions

P: n/a
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\guest'...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
Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a

"Phil" <Ph**@nospam.com> wrote in message
news:d4**********@nwrdmz01.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\guest'...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/default...b;en-us;319930

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

Simon

Jul 23 '05 #2

P: n/a
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 (Boolean& isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.Get PooledConnection(SqlConnectionString
options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen(IDbConn ection connection,
ConnectionState& originalState)
at System.Data.Common.DbDataAdapter.FillFromCommand(O bject 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\HelpDesk\SQLHelpDesk\HelpSC.vb:line 72
at SQLHelpDeskClient.HDeskClient.Page_Load(Object sender, EventArgs e) in
c:\inetpub\wwwroot\SQLHelpDeskClient\Default.aspx. vb:line 56

I use a standard SQL connection string:-

sqlcnn = New SqlConnection("data source=(local)\NetSDK;" & _
"initial catalog=TsHelpDesk;integrated 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" <sq*@hayes.ch> wrote in message
news:42**********@news.bluewin.ch...

"Phil" <Ph**@nospam.com> wrote in message
news:d4**********@nwrdmz01.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\guest'...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/default...b;en-us;319930

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

Simon

Jul 23 '05 #3

P: n/a
"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

Jul 23 '05 #4

P: n/a
"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

Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.