{"id":430,"date":"2017-09-06T19:43:14","date_gmt":"2017-09-06T15:43:14","guid":{"rendered":"https:\/\/bukleev.ru\/?page_id=430"},"modified":"2018-12-14T07:58:01","modified_gmt":"2018-12-14T03:58:01","slug":"%d1%81%d0%b2%d1%8f%d0%b7%d0%b0%d0%bd%d0%bd%d1%8b%d0%b9-%d1%81%d0%b5%d1%80%d0%b2%d0%b5%d1%80-mysql","status":"publish","type":"page","link":"https:\/\/bukleev.ru\/?page_id=430","title":{"rendered":"\u0421\u0432\u044f\u0437\u0430\u043d\u043d\u044b\u0439 \u0441\u0435\u0440\u0432\u0435\u0440 mysql"},"content":{"rendered":"<p>&nbsp;<\/p>\n<pre><strong>\r\n\u041f\u0440\u0438\u043c\u0435\u0440 \u0441\u043a\u0440\u0438\u043f\u0442\u0430 \u0434\u043b\u044f Mysql.<\/strong>\r\n\r\nEXEC master.dbo.sp_addlinkedserver @server = N'OTRS', @srvproduct=N'MySQL', @provider=N'MSDASQL', @datasrc=N'OTRS', @provstr=N'ODBC;DSN=OTRS;', @catalog=N'otrs'\r\n \/* For security reasons the linked server remote logins password is changed with ###### *\/\r\nEXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'OTRS',@useself=N'False',@locallogin=NULL,@rmtuser=N'root',@rmtpassword='########'\r\nGO\r\n\r\nEXEC master.dbo.sp_serveroption @server=N'OTRS', @optname=N'collation compatible', @optvalue=N'true'\r\nGO\r\n\r\nEXEC master.dbo.sp_serveroption @server=N'OTRS', @optname=N'data access', @optvalue=N'true'\r\nGO\r\n\r\nEXEC master.dbo.sp_serveroption @server=N'OTRS', @optname=N'dist', @optvalue=N'false'\r\nGO\r\n\r\nEXEC master.dbo.sp_serveroption @server=N'OTRS', @optname=N'pub', @optvalue=N'false'\r\nGO\r\n\r\nEXEC master.dbo.sp_serveroption @server=N'OTRS', @optname=N'rpc', @optvalue=N'true'\r\nGO\r\n\r\nEXEC master.dbo.sp_serveroption @server=N'OTRS', @optname=N'rpc out', @optvalue=N'true'\r\nGO\r\n\r\nEXEC master.dbo.sp_serveroption @server=N'OTRS', @optname=N'sub', @optvalue=N'false'\r\nGO\r\n\r\nEXEC master.dbo.sp_serveroption @server=N'OTRS', @optname=N'connect timeout', @optvalue=N'0'\r\nGO\r\n\r\nEXEC master.dbo.sp_serveroption @server=N'OTRS', @optname=N'collation name', @optvalue=null\r\nGO\r\n\r\nEXEC master.dbo.sp_serveroption @server=N'OTRS', @optname=N'lazy schema validation', @optvalue=N'false'\r\nGO\r\n\r\nEXEC master.dbo.sp_serveroption @server=N'OTRS', @optname=N'query timeout', @optvalue=N'0'\r\nGO\r\n\r\nEXEC master.dbo.sp_serveroption @server=N'OTRS', @optname=N'use remote collation', @optvalue=N'true'\r\nGO\r\n\r\nEXEC master.dbo.sp_serveroption @server=N'OTRS', @optname=N'remote proc transaction promotion', @optvalue=N'true'\r\nGO<\/pre>\n<p>&nbsp;<\/p>\n<p>\u041d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e \u0435\u0449\u0435 \u0434\u043e\u0431\u0430\u0432\u0438\u0442\u044c \u0432 ODBC \u0437\u0430\u043f\u0438\u0441\u044c \u043e\u0442 \u0438\u043c\u0435\u043d\u0438 \u0430\u0434\u043c\u0438\u043d\u0438\u0441\u0442\u0440\u0430\u0442\u043e\u0440\u0430.<\/p>\n<p>\u0415\u0441\u043b\u0438 \u043e\u0442 \u0438\u043c\u0435\u043d\u0438 \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044f \u043f\u0440\u0438\u043b\u0438\u043d\u043a\u043e\u0432\u0430\u043d\u043d\u044b\u0439 \u0441\u0435\u0440\u0432\u0435\u0440 \u043e\u0442\u043a\u0440\u044b\u0432\u0430\u0435\u0442\u0441\u044f \u043d\u043e\u0440\u043c\u0430\u043b\u044c\u043d\u043e, \u0430 \u043f\u0440\u0438 \u0437\u0430\u043f\u0443\u0441\u043a\u0435 \u0432 SQL \u0430\u0433\u0435\u043d\u0442\u0435 \u043e\u0448\u0438\u0431\u043a\u0430, \u0442\u043e \u0441\u043c\u043e\u0442\u0440\u0438\u043c <a href=\"https:\/\/stackoverflow.com\/questions\/1362108\/cannot-initialize-the-data-source-object-of-ole-db-provider-msdasql-for-linked#1362108\"><strong>\u044d\u0442\u043e\u0442 \u043e\u0442\u0432\u0435\u0442<\/strong><\/a><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-799\" src=\"https:\/\/bukleev.ru\/wp-content\/uploads\/2018\/07\/error.jpg\" alt=\"\" width=\"677\" height=\"810\" srcset=\"https:\/\/bukleev.ru\/wp-content\/uploads\/2018\/07\/error.jpg 677w, https:\/\/bukleev.ru\/wp-content\/uploads\/2018\/07\/error-251x300.jpg 251w\" sizes=\"auto, (max-width: 677px) 100vw, 677px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>The problem comes because the Temp folder of the User under which the SQL server service is running isn&#8217;t accessible under the credentials which the query is running. Try to to set the security of this temp folder with minimal restrictions. The dsn that gets created every time you run an openrowset query then can be recreated without any credentials conflict. This worked for me without any restart requirements.<\/p>\n<table width=\"660\">\n<tbody>\n<tr>\n<td width=\"15\">\n<table>\n<tbody>\n<tr>\n<td>2<\/td>\n<td width=\"1\"><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/td>\n<td>Where would I find this Temp folder?\u00a0\u2013\u00a0<a href=\"https:\/\/stackoverflow.com\/users\/356544\/slider345\">Slider345<\/a>\u00a0<a href=\"https:\/\/stackoverflow.com\/questions\/1362108\/cannot-initialize-the-data-source-object-of-ole-db-provider-msdasql-for-linked#comment19689387_3262242\">Jan 7 &#8217;13 at 17:37<\/a><\/td>\n<\/tr>\n<tr>\n<td width=\"15\">\n<table>\n<tbody>\n<tr>\n<td>3<\/td>\n<td><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/td>\n<td>For our specific configuration &#8212; Windows Server 2008 R2, SQL Server 2008 R2 &#8212; the specific TEMP folder we needed to share with domain users was:\u00a0C:\\Users\\&lt;SQL Server Service Account Name&gt;\\AppData\\Local\\Temp\u00a0\u2013\u00a0<a href=\"https:\/\/stackoverflow.com\/users\/1313837\/fresh\">fresh<\/a>\u00a0<a href=\"https:\/\/stackoverflow.com\/questions\/1362108\/cannot-initialize-the-data-source-object-of-ole-db-provider-msdasql-for-linked#comment28739790_3262242\">Oct 15 &#8217;13 at 20:50<\/a><\/td>\n<\/tr>\n<tr>\n<td width=\"15\">\n<table>\n<tbody>\n<tr>\n<td>1<\/td>\n<td><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/td>\n<td>I&#8217;ve spent all day on this &#8212; I can&#8217;t believe this is the solution!!! But it worked so I am happy.\u00a0\u2013\u00a0<a href=\"https:\/\/stackoverflow.com\/users\/2066625\/warren\">Warren<\/a>\u00a0<a href=\"https:\/\/stackoverflow.com\/questions\/1362108\/cannot-initialize-the-data-source-object-of-ole-db-provider-msdasql-for-linked#comment33677340_3262242\">Mar 5 &#8217;14 at 0:59<\/a><\/td>\n<\/tr>\n<tr>\n<td>\n<table>\n<tbody>\n<tr>\n<td><\/td>\n<td><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/td>\n<td>Without the permission fresh shared, you will only be able to run the script locally by open SSMS on the SQL Server. Another way is to add your own domain account to the local (SQL Server) Administrators group, which does the same trick.\u00a0\u2013\u00a0<a href=\"https:\/\/stackoverflow.com\/users\/1303588\/chjquest\">Chjquest<\/a>\u00a0<a href=\"https:\/\/stackoverflow.com\/questions\/1362108\/cannot-initialize-the-data-source-object-of-ole-db-provider-msdasql-for-linked#comment58665462_3262242\">Feb 18 &#8217;16 at 15:48<\/a><\/td>\n<\/tr>\n<tr>\n<td>\n<table>\n<tbody>\n<tr>\n<td><\/td>\n<td><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/td>\n<td>Thank you so much, this answer has saved us so much time!\u00a0\u2013\u00a0<a href=\"https:\/\/stackoverflow.com\/users\/403424\/martynjones87\">MartynJones87<\/a>\u00a0<a href=\"https:\/\/stackoverflow.com\/questions\/1362108\/cannot-initialize-the-data-source-object-of-ole-db-provider-msdasql-for-linked#comment59757680_3262242\">Mar 17 &#8217;16 at 8:34<\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>(\u041d\u0443\u0436\u043d\u043e \u0434\u043e\u0431\u0430\u0432\u0438\u0442\u044c \u043f\u0440\u0430\u0432\u0430 \u0434\u043b\u044f \u043f\u0430\u043f\u043a\u0438 Temp)<\/p>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; \u041f\u0440\u0438\u043c\u0435\u0440 \u0441\u043a\u0440\u0438\u043f\u0442\u0430 \u0434\u043b\u044f Mysql. EXEC master.dbo.sp_addlinkedserver @server = N&#8217;OTRS&#8217;, @srvproduct=N&#8217;MySQL&#8217;, @provider=N&#8217;MSDASQL&#8217;, @datasrc=N&#8217;OTRS&#8217;, @provstr=N&#8217;ODBC;DSN=OTRS;&#8217;, @catalog=N&#8217;otrs&#8217; \/* For security reasons the linked server remote logins password is changed with ###### *\/ EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N&#8217;OTRS&#8217;,@useself=N&#8217;False&#8217;,@locallogin=NULL,@rmtuser=N&#8217;root&#8217;,@rmtpassword=&#8217;########&#8217; GO EXEC master.dbo.sp_serveroption @server=N&#8217;OTRS&#8217;, @optname=N&#8217;collation compatible&#8217;, @optvalue=N&#8217;true&#8217;<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"open","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-430","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/bukleev.ru\/index.php?rest_route=\/wp\/v2\/pages\/430","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bukleev.ru\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/bukleev.ru\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/bukleev.ru\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bukleev.ru\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=430"}],"version-history":[{"count":6,"href":"https:\/\/bukleev.ru\/index.php?rest_route=\/wp\/v2\/pages\/430\/revisions"}],"predecessor-version":[{"id":800,"href":"https:\/\/bukleev.ru\/index.php?rest_route=\/wp\/v2\/pages\/430\/revisions\/800"}],"wp:attachment":[{"href":"https:\/\/bukleev.ru\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=430"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}