[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Linked Server Connection and Database Permissions?

Posted on 2007-09-28
8
Medium Priority
?
529 Views
Last Modified: 2012-05-05
I've changed the linkedserver code from 'Integrated Security' to 'UserId/Password'.

Now I cannot run the sql query.

Q. Do I have to login to the db as well with more code?

[Current Code]
EXEC sp_addlinkedserver
@server = 'TESTPC',
@srvproduct = '',
@provider = 'SQLNCLI',
@provstr = 'DRIVER={SQL Server};SERVER=TEST01\SQLEXPRESS;User Id=TESTPC;Password=123;'  // Link works

Select * From TESTPC.HousingDatabase.dbo.SalesTable // This code now fails

Help...


0
Comment
Question by:kvnsdr
  • 4
  • 4
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19981844
>// This code now fails

with what error?
0
 
LVL 1

Author Comment

by:kvnsdr
ID: 19982054
I need to include more explaination.

I'm trying to connect from a WinApp and having trouble since I change the 'Integrated Security'.

[More code]
cn = new SqlConnection(@"server=(local)\SQLEXPRESS; persist security info=False;integrated security=SSPI;initial catalog=HousingDatabase");
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn; cn.Open();
cmd.CommandText =
" If Not Exists(select * from master..sysservers where srvname = TESTPC) " +
" Begin Exec sp_addlinkedserver @server = 'TESTPC', @srvproduct = '', @provider = 'SQLNCLI', " +
" @provstr = 'DRIVER={SQL Server};SERVER=TESTPC\SQLEXPRESS;User Id=testpcadministrator;Password=x;' END";
cmd.ExecuteNonQuery();

cmd.CommandText = " Select * From TESTPC.HousingDatabase.dbo.SalesTable";
cmd.ExecuteNonQuery();

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19982123
I can only repeat: what is the error, and on which line does it error out exactly?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:kvnsdr
ID: 19982261
oops,

"SqlException: Login failed for user ' '. The user is not accociated with a trusted SQL Server connection."

I have added account 'admindev' to the test pc (testpcadministrator) SQL Server as dbo, sysadmin, mapped to HousingDatabase, but the above error seems to say that NO user is named to login.

I'm so used to using Windows Integrated that I'm lost without it.

Just thought it would be more secure to have users actually login to SQL Server without having to make them Windows users too.

I think I  may need to code C# to login to the remote as well?



 

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 750 total points
ID: 19983294
>I have added account 'admindev' to the test pc (testpcadministrator) SQL Server as dbo, sysadmin, mapped to HousingDatabase, but the above error seems to say that NO user is named to login.#
actually, that remote server is probably configured to only accept windows logins, check that setting. if it is not in mixed authentication mode, sql logins will not be accepted (although you can create them).



now: why don't you create the linked server with windows authentication anyhow, as you love that?

cn = new SqlConnection(@"server=(local)\SQLEXPRESS; persist security info=False;integrated security=SSPI;initial catalog=HousingDatabase");
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn; cn.Open();
cmd.CommandText =
" If Not Exists(select * from master..sysservers where srvname = TESTPC) " +
" Begin Exec sp_addlinkedserver @server = 'TESTPC', @srvproduct = '', @provider = 'SQLNCLI', " +
" @provstr = 'Provider=SQLOLEDB;SERVER=TESTPC\SQLEXPRESS;integrated security=SSPI;' END";
cmd.ExecuteNonQuery();

cmd.CommandText = " Select * From TESTPC.HousingDatabase.dbo.SalesTable";
cmd.ExecuteNonQuery();

0
 
LVL 1

Author Comment

by:kvnsdr
ID: 19991414
>Just thought it would be more secure to have users actually login to SQL Server without having to make them Windows users too.<

Just tell me if I'm asking for the impossible considering my C# coding method.


0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19991568
well, if you want to use sql logins on the remote server, ensure that the remote sql server is in mixed authentication method.
0
 
LVL 1

Author Comment

by:kvnsdr
ID: 20006471
I'll simply use the Windows Authentication as it seems to be the only solution to this particular issue.

Thanks for all your help.........
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question