SQL 2012 :: Linked Server And Remote Login Password
Oct 7, 2014Is there any way to find out the password for the remote login of the Linked server
View 1 Replies
ADVERTISEMENT
SQL Server 2012 :: Hardcoded Login And Password In Proc
Jul 22, 2015I have a procedure where Login and Password of the database are to be used and right now i have given a valid hardcoded value for them.
How can I get these values from the actual login (I mean sql server authentication value or windows authentication).
How Can I Login To My MS SQL Server 2005 If I Forgot My Login Password
Oct 8, 2007
Hi,
I am using SQL Server 2005 at home.
My problem is I forgot my password to log in to my server.
I only remember user name is 'sa'.
I haven't used it for two to three months. So I forgot password.
Previously, When I used sql server 2000, my login is as windows login. so no problem.
But this time, I set seperate log in and I got this problem.
Any help will be appreciated.
Thanks
George
Linked Server And Password
Apr 8, 2008
Hello guys,
I have developed a stored procedure that create linked server. The problem is:
to crearte the linked server I need to pass user and password. How can I pass user and password in a encrypted way.
I think that if the stored proc execute: addlinkedserver procedures with user and password, these are readable to everybody with sniffer or SQL profile.
How can I pass encrypted password to SQL server engine to create a linked server?
Thank
Linked Server And Password
Apr 8, 2008
Hello guys,
I have developed a stored procedure that create linked server. The problem is:
to crearte the linked server I need to pass user and password. How can I pass user and password in a encrypted way.
I think that if the stored proc execute: addlinkedserver procedures with user and password, these are readable to everybody with sniffer or SQL profile.
How can I pass encrypted password to SQL server engine to create a linked server?
Thank
Copy Linked Server Password
Sep 28, 2006Is there a way in SQL2005 to copy the linked server password from SQL2000?
We are about to copy our SQL 2000 database to SQL 2005 (and change operating systems and make other changes to the system). We can't do an upgrade in place because of the other changes, plus we want to have the original in case we can't get it updated before the start of business the next day.
I plan to move the databases by copying the files and attaching them, and I have a program that pulls users and encrypted passwords and adds them to the new system without encrypting the already encrypted passwords. Jobs I can script and run on the new system, DTS jobs I can open and save to the new system, and linked servers I can get the names of and link on the new server.
In SQL 2000 I would have "cheated" and copied the "DTS" and "Jobs" tables from one server to the other, but SQL 2005 has made it more complicated, so I will do it the slower way.
The problem comes with the linked server passwords. While I can see the linked server data in the master system tables, including the names and that there is an encrypted password. The problem is I don't know the passwords, so I can't enter it myself, and I can't update the system table, so I can't insert it as is. And sp_addlinkedsrvlogin does not have an option to not re-encrypt the password (like @encryptopt = 'skip_encryption' in sp_addlogin).
Is there a way to figure out the password, or copy/insert the password without having it re-encrypt? In SQL2000 I would have done:
update A set
name = b.name,
password = b.password
from sysxlogins A, sysxlogins B
where A.srvid = 22
and B.srvid = 11
But SQL 2005 will not allow you to do this kind of update (allow updates has no effect).
Thanks, Tim
PS - Anything I might have missed that anyone can suggest, or any faster method of copying the database? We have a limited maintenance window to do this upgrade, so anything I can do ahead of time will help. Thanks again. Tim
Sql Server Login Name And Password
Oct 6, 2005Hi,I have developed site which has a resource file which connects to sqlserver using login name and password.Now my client doesn't want me to login to production database. bu the packing of the system is with me. is there any way to solve this issuse so that i can package the software without knowing database login name and password.TIAAmit
View 2 Replies View RelatedNotification When SQL Server Login Password Is Set To Expire
Jan 17, 2008Is there a way to set up automated email notification beginning 14 days prior to when a SQL Server Login password (that has "Enforce password expiration" enabled) will expire?
Thanks,
-Dave
SQL Server Express Client App Login / Password
Dec 18, 2006Help!!
I used to have an Access database which had tables for users, roles, actions etc.
This was used by a C++ client app (using ADO) which logged in, got the user ID and password (by raising a login dialog ) and then checked these against a user table and then assigned the roles and possible actions.
Now we have SWL Server Express 2005 - NT Authorisation - how do I get/pass the user ID to the C++ Application so it can get the associated roles? Seems silly to have 2 logins.
Better still can I do away with the App's User table or make its password column invisible to all users bar Admin and the C++ App?
Transact SQL :: Linked Server And LDAP - Get Password Expiration Date
Oct 29, 2015I have configured a Linked Server with Active Directory (LDAP), working properly.
Now, through this Linked Server, I need information about password expiration date in my AD Domain accounts.
SQL Server 2014 :: How To Encrypt And Check A Login Password
Jul 6, 2014I am trying to learn how to store a web form password and than check it when the user log in. So far none of the code I can find works.
Why the following test does not work and what the correct code should be?
Insert Into [user]
values ('name', 'email', HashBytes('SHA1', 'bob'))
GO
Why does the following produce no rows?
SELECT *
From [user]
Where HashBytes('SHA1', password) = HashBytes('SHA1', 'bob')
Using 'Alter Login' To Change Password In SQL Server 2005
Sep 26, 2006Please Help!!
We have an application using SQLOLEDB connection to a SQL Server 2005 database. Per domain policy, the users are required to change their password every 60 days.
The accounts are established to 'Enforce password policy'.
When we try to execute the 'ALTER LOGIN' command to change the password, locks are being established and will not free the account without bouncing the instance.
After issuing the command, any interaction with the server using this UserID results in a "lock request time out" error 1222.
I have tried issuing this command using both the application and through SQLServer Mgmt Studio Express and the results are the same.
Any idea would be greatly appreciated.
Rusty Rickmon
Linked Remote Server
Mar 23, 2000Hello all SQL Server 7 gurus,
I need your help!!! I can use SELECT to query a table on the remote server but
I can't use UPDATE,DELETE OR INSERT. Please tell me how to get around this problem.
Thanks in advance.
PS. The remote server is added by using sp_addlinkedserver
Linked Remote Server
Feb 11, 2005I need to be able to query remote server tables and join them to local tables. Thought that linked server would be best so that I can use local stored procs for parameters if needed. I have no administrative control over remote server. I have been given select permissions only to one db on remote server. Both servers are sql2000, local machine has spk3.
I have established the remote server in network client utility. I can register the remote server in EM, look at tables. I can run select statements against the remote server in QA when I log in and specify that server. I can reference an odbc data connection with web pages and display rowsets.
Next, I created a linked server under security, using sql (since it's an sql 2000 db) with the same name used above in the client utility and the em. I created a local login with the same name and password as the login used successfully above (Paragraph 2). On the security tab, I've used sa as the local login and entered the remote user and password, using the default 'be made without using a security context'. When I try to view the tables (which I can do through registered) or run this through QA:
SELECT * FROM OPENQUERY(CASCE, 'SELECT person_pk, lastname FROM dbName.dbo.tblName (NOLOCK)'),
I get: sql error 17: sql server does not exist or access denied
If I edit security, wipe out the top settings and specify the security context below using the login information, same thing. Tried messing around with different security contexts, no help or ole db error.
Any help? In addition, any tutorials out there a little more user friendly than the bol? I think I want to use the linked server option and stored procs.
How To Create A Login Userid And Password For Ms SQL Server 2005 Express?
Oct 30, 2007My PC is Window XP Pro and I'm using Microsoft SQL Server 2005 Express and Microsoft SQL Server Management Studio Express.
My question is how to create a login userid and password under "SQL Server Authentication"? (as shown in http://www.findingsteve.net/print_screen.jpg)
Any tutorial about this I can read?
I Can't Change My Sql Login User's Password In SQL Server 2005 Express
Jan 13, 2006SQL Server 2005 Express keeps putting in a different password than the one I chose. I would check the properties on the login I want to change. Then I change the password and it gets accepted. When I try my web application, I get the dreaded "login failed for <loginname>". I look at the properties again and see my password never change. Is this a bug? I ever tried this syntax to no avail:
CREATE LOGIN <loginname> WITH PASSWORD='<mypassword>' CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
SQL Security :: What Is Alternative Of A Login Whose Password Goes Missing In Server 2008 R2
Jun 5, 2015We have a situation where SSRS reports are using Testuser account to run the SSRS reports for every user who has access to these reports.Now developers need the password for this login Testuser to proceed with their ssrs reports development, but its sort of mess that no one has password ever saved.So in case is there a way it can be retrieved or what should be an alternative if password cannot be retrieved for that login, as i believe changing the password will break the reports using the current password?
View 7 Replies View RelatedLinked Server Not Using Remote Index?
Jul 10, 2013Problem ms sql server 2012 not using remote index on sql server 2000 linked server with odbc
Let say my servers are:
srvdb and remotesrv
when do select on srvdb:
select * from remotesrv.database.dbo.inventory this select not using index on remoteserver
but if this select is on remoteserver like
select * from database.dbo.inventory, this select use index PK_Inventory (clustered)
Remote Server Login
Jun 30, 2004I got a problem a system administrator can not log on to sql remote server so he can back up the system tables. Hes doing it through active directory, not sure what the problem is. can anyone help please. reason says not associated with a trusted sql connection?
View 7 Replies View RelatedRemote Login Into Server
Nov 27, 2007Hello,
We have a SQL Server 2005. I am able to remote login into the server using my desktop user/password. But when I am unable to login using sa/password. It says , check the username , domain etc.
what chagnes do I have to make so , that I can remote login into the server from my desktop using the SA/password.
Thanks
Alter Login Fails When Login Changes His Own Password
Apr 11, 2008
I have seen alot of comments posted about an Issue in SQL2005 that no one at microsoft could really answer. If a user would try to alter his own login, it would fail stating a permissions error.
This is what I did to get it to work .
USE my_dataBase
GO
Alter Login [my_login] WITH PASSWORD = 'newpassword' OLD_PASSWORD = 'oldpassword'
In previous examples, people were trying to do an alter login without first narrowing it to the database.
If you do not include the USE statement it will fail.
Another thing to note is that they can only change certain things....they cannot turn off check_policy or check_expiration.
Happy Coding
Regarding Login In Linked Server
Dec 12, 2006We are using linked servers to access another server, we are using windows authentication. but when we use
EXEC master.dbo.sp_addlinkedserver @server = 'SERVER', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = 'SERVER', @locallogin = NULL, @useself = N'False', @rmtuser = N'sa', @rmtpassword = N'sa'
When we connect using a SQL login as specified in the above line we are able to connect to the linked server.
But when we want to connect using a Windows user it is connecting.(we want to connect using a specific windows user i.e only one windows user will be there in the server and we shall access the server using that windows user)
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = 'SERVER', @locallogin = NULL, @useself = N'False', @rmtuser = N'Domainuserid', @rmtpassword = 'password'
Is there any other way to connect to the linked serrver.. we dont want to use the SQL server login to connect to the linked server.. how to use windows authentication to connect to the linked server. Anyone have tried it out.. Thanks in advance
Difference Between Remote Server And Linked Servers?
Oct 25, 2000Hi Everybody,
Can anyone tell me what is the difference between Remote Server and Linked Servers.
thks in advance,
Srinivasan.
Linked Server: Works Local, But Not Remote
Nov 14, 2007I have created a linked server (to an AS/400) via MSDASQL. It works fine when I execute queries locally (i.e. from Management Studio running on the SQL 2005 server).
However, when I execute a query from a remote machine I get an error (see below for the exact message).
I know the ODBC connection is between the SQL server and the AS/400, and that the remote client does not have connectivity to the AS/400. But shouldn't the SQL server be able to pass the query through to the linked server even if the query is initiated from a remote machine?
Does anyone have any suggestions on how I can resolve this problem so that queries against the linked server can be executed from remote machines?
Errors:
OLE DB provider "MSDASQL" for linked server "MERCURY" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "MERCURY" returned message "[IBM][iSeries Access ODBC Driver]Key value in connection string too long.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MERCURY".
Environment: SQL 2005, Standard Edition SP2
Thanks in advance for any suggestions.
David Rueter
drueter@assyst.com
Remote Sql Server Login Fails
Aug 4, 2004Hello,
I'm about googled out on this one. I did find one post that looked almost like mine but the thread was never finished. I'm sure this problem has been solved and I'm just having trouble finding it. Can someone help me?
I'm running sql server 2000 on a small workgroup network of winxp machines.
If sql server machine is the same as webserver machine, my asp.net app works. If sql server and webserver are different, the app fails because of sql server login failure.
I am able to explore the database remotely through server explorer in VStudio2003. However, when I try to access the database with the asp.net application, the login fails. I have tried several connection strings. Authentication on the server is 'mixed', the server runs in the system account, and the password for sa is "" (blank password).
I've tried (for instance)
Application["DBConnectString"] = "server=machinename;uid=sa;pwd=;database=Resume";
and
Application["DBConnectString"] = "server=machinename;integrated security=true;database=Resume";
When "machinename" is the same as the machine on which the app is running. both of those connection strings work, and the application runs correctly.
When "machinename" is different (that is, sql server on a different machine from the webserver), depending on the connection string I get either
"Login failed for user 'sa'."
or
"Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."
Obviously VStudio is using some mechanism to add the remote server to it's list of servers and then to allow me manually to explore the databases on that server. Why am I too stoopid to figure it out?
TIA,
Tim
Login To Remote SQL Server Fails
May 30, 2000I am trying to use a confirmed and valid account over a WAN (US to Europe)
to register and access a SQL server database. The protocol being used is
TCP/IP and it has been confirmed that port 1433 is open.
The data is going through a frame relay line and pining the remote
SQL Server does return a valid response. Unfortunately, I am getting the
following error:
Error 18456
Severity Level 14
Message Text
Login failed for user 'TestUser'.
Explanation
You do not have permission to log in to the server.
Action
Contact a member of the sysadmin fixed server role to request login permission.
Login To Remote SQL Server Fails --- #2
May 30, 2000I am using TCP/IP net library as far as I know. Just in case though, how
can I ensure that TCP/IP is being used? both on the local and
remote servers?
Thanks for your help
Ziggy
Multiple Login For Linked Server
Apr 15, 2014Can we assign two logins for two different databases on target server. For example, I would like to create a Linked Server between ServerA and ServerB.
ServerB has two databases on it and both using different logins to access. Say DB1 has Login1 and DB2 has Login2
So can I create something like this:
EXEC master.dbo.sp_addlinkedserver @server = N'db_link', @srvproduct=N'SQLServer OLEDB Provider', @provider=N'SQLNCLI', @datasrc=N'ServerB'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'db_link',@useself=N'False',@locallogin=N'Login',@rmtuser=N'Login1',@rmtpassword='Password1'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'db_link',@useself=N'False',@locallogin=N'login',@rmtuser=N'Login2',@rmtpassword='Password2'
Am I able to access two different databases using this same linked server. I tried but with no success.
Login Failure On A Linked Server
Jan 31, 2008I have a stored procedure that retrieves data via a linked server that is running on a local instance of SQL Express 2005. This has been working without problems for some time.
We are now trying to move the database with the links to either SQL Express 2005 running on a server, or an instance of SQL Server 2005. In both cases I can successfully link the servers but when the stored procedure executes it fails on the select statement from the linked server with the following message:
OLE DB provider "SQLNCLI" for linked server "mosier" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
Why is the user name null? I've tried explicitly setting login information by running the sp_addlinkedsrvlogin procedure but get the same results. In all cases logins should be using windows authentication and all the servers are inside the corporate firewalls.
I presume there is some property somewhere that is different between the SQL instances that work and the ones that don't but I haven't been able to find anything.
How To Return A Varchar From Sproc On Remote Linked Server?
Aug 22, 2007The following T-SQL 2005 snippet executes a remote sproc on an Oracle database.
DECLARE @OutParam VARCHAR(5)
EXEC ('{Call getnextpin(1)}') AT ALTTEST
GetNextPin takes a single input parameter.
I need to modify the above code snippet to return the next PIN number into @OutParam.
Any ideas?
Any help appreciated.
Regards,
Paul.
Server Aliases/linked Servers Cause Remote Queries
Apr 4, 2008Can someone please shed some light on what seems to me to be a common requirement.
If I create an alias or linked server to Server1 - say Alias1 - on Server1 and then use that name in a query on Server1, a remote/distributed query is always used (even though we are running on the local server and that overhead is completely unnecessary).
Is SQL Server really not capable of deciding that
select * from Alias1.db1.dbo.table1
and
select * from Server1.db1.dbo.table1
should be optimized and executed exactly the same when Alias1 is Server1, but that it is a distributed query ONLY when Alias1 is really referring to a remote server? I realize that the four part name is not necessary when I am referring to objects on the current server, but I am trying to write code that is server instance independent.
It just seems that if that is not possible, then the only way to create system independent stored procs that can run in dev, staging, and production environments and work with multiple databases on multiple servers is to create all sorts of scripts to regenerate all the procs whenever you move a database between servers?
If SQL Server is even close to the enterprise big iron server that MS now claims it is, it surely needs to support running in dev, staging, and production environments and work with multiple databases on multiple servers?!
I'm really looking for someone to tell me I'm missing something simple, and of course you can do this - but complex workarounds are invited too :-)
This is not something I am investigating as an academic exercise, I am already doing this, but I have to figure out how to do it better because with all these unnecessary distributed queries, performance is horrible.
Cannot Connect To Remote Distributor -- Linked Server Error
Mar 8, 2007I have 2 servers: #1 -- SQL 2005 SP1 publisher ; #2 -- SQL 2005 SP2 subscriber
originally I had #1 as pub and dist but dist but killing my CPU so I was in the process of moving the dist to #2... Got it all configured and when I tried to add #2 as a dist for #1 it fails when I get to the administrative password screen... It give me an error about how it cannot connect with the given password but under that is says linked server failed.
Since it mentioned linked server I tried to connect via the previous linked server to #2 and it failed... I cannot connect to #2 anymore from #1. I can login directly to #2 and I can use osql to connect to #2 but linked server does not work. I tried all drivers and many configurations...
Any ideas??
Calling A Function From A Remote (linked Server) Problem
May 21, 2008
Hi guys,
I am trying to call a function from a remote sql server (linked server) using the following syntax:
select [ServerName].[dbName].dbo.CLRHelloWorld('SomeMessage'). I am getting the following error:
The object name '[ServerName].[dbName].dbo.' contains more than the maximum number of prefixes. The maximum is 2.
I need to call this function remotely and using this syntax; I don't want to use OPENQUERY...
Any hints?
Yousef